Some web services only seem to return the 2 global dimension values and don't include a dimension set ID to reference the additional values.
Customer Ledger Entries for instance includes a dimension_set_id field which I was able to expand via Power Query into as many separate columns as needed, but have not been able to figure out how the Posted General Journals correspond to the additional dimension values. They're clearly linked somehow, since you can see the full list of values in the Dimension summary section to the right when reviewing the entries within BC.
I'm hoping there's another query that I can use to cross reference the entries, but I'm struggling to figure out what that would be from the fields that are currently being exported.
Thank you in advance for any feedback.
You either have to create a new web service against the Table object 181 and publish that object, or use an existing web service object (if there is one) and add those missing fields in my screenshot.
Weird, I don't seem to have that option!
181 is Additional Customer Terms for us.
I tried adding a new Web Service page, but couldn't find "Posted Gen. Journal Line" as an option.
Thanks so much for the response though. I'll do some additional searching to see if there's something that I'm missing in order to get that.
Hi
I think you are looking for table 181 "Posted Gen. Journal Line" see the screenshot below -
Good Luck!
In the meantime I'll share the Power Query code that I use to expand the Dimension Sets, in case anybody finds it of value. I'm no expert with the M language, so I'd imagine that it could be done in fewer lines, but here you go anyway...
let Source = OData.Feed("api.businesscentral.dynamics.com/.../[ID]/Production/ODataV4/Company('[COMPANY]')/DimensionSetEntries", null, [Implementation="2.0"]), in #"Remove EVENT" |
Just update the Source line at the beginning with your own OData feed, then adjust the dimension names accordingly.
For example, if your dimension was named DEPARTMENT instead of DEPT or whatever else, just replace DEPT with DEPARTMENT, and so on.
#"Add DEPT" = Table.AddColumn(#"Removed Duplicates", "DEPT", each "DEPT"), would become #"Add DEPARTMENT" = Table.AddColumn(#"Removed Duplicates", "DEPARTMENT", each "DEPARTMENT"), |
Note that the first line of code for the next dimension will reference the previous line, so DEPT gets listed once within the PROJECT lines, and then the "in" at the bottom will be whatever you end with.
You can then of course keep copying those rows down and adding more dimensions, just remember to add a comma to the last line before you paste it in, or you'll have an error in your code.
That's it. You can now merge that into whatever other source that you've got which has a department_set_id and have as many dimension columns as you'd like!
Additional references:
The examples below just pivot the dimension sets directly which is much simpler. I used the method above because we only wanted certain dimensions in certain reports. Although you could just merge and only expand the dimension columns you need in each case... so maybe it was that I wanted both the dimension codes and values that I did it this way.
Data model in Power BI and Dimension Set ID - Dynamics 365 Business Central Forum Community Forum
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
André Arnaud de Cal... 291,188 Super User 2024 Season 2
Martin Dráb 230,030 Most Valuable Professional
nmaenpaa 101,156