Dynamics Community Forum Thread Details

OData - Get Dimension Set ID for Posted General Journals?

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.

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"]),
#"Removed Other Columns" = Table.SelectColumns(Source,{"Dimension_Set_ID"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
#"Add DEPT" = Table.AddColumn(#"Removed Duplicates", "DEPT", each "DEPT"),
#"Merge DEPT" = Table.NestedJoin(#"Add DEPT", {"Dimension_Set_ID", "DEPT"}, DimensionSets, {"Dimension_Set_ID", "Dimension_Code"}, "DimensionSets", JoinKind.LeftOuter),
#"Expand DEPT" = Table.ExpandTableColumn(#"Merge DEPT", "DimensionSets", {"Dimension_Value_Code", "Dimension_Value_Name"}, {"DEPT Code", "DEPT Name"}),
#"Remove DEPT" = Table.RemoveColumns(#"Expand DEPT",{"DEPT"}),
#"Add PROJECT" = Table.AddColumn(#"Remove DEPT", "PROJECT", each "PROJECT"),
#"Merge PROJECT" = Table.NestedJoin(#"Add PROJECT", {"Dimension_Set_ID", "PROJECT"}, DimensionSets, {"Dimension_Set_ID", "Dimension_Code"}, "DimensionSets", JoinKind.LeftOuter),
#"Expand PROJECT" = Table.ExpandTableColumn(#"Merge PROJECT", "DimensionSets", {"Dimension_Value_Code", "Dimension_Value_Name"}, {"PROJECT Code", "PROJECT Name"}),
#"Remove PROJECT" = Table.RemoveColumns(#"Expand PROJECT",{"PROJECT"}),
#"Add EVENT" = Table.AddColumn(#"Remove PROJECT", "EVENT", each "EVENT"),
#"Merge EVENT" = Table.NestedJoin(#"Add EVENT", {"Dimension_Set_ID", "EVENT"}, DimensionSets, {"Dimension_Set_ID", "Dimension_Code"}, "DimensionSets", JoinKind.LeftOuter),
#"Expand EVENT" = Table.ExpandTableColumn(#"Merge EVENT", "DimensionSets", {"Dimension_Value_Code", "Dimension_Value_Name"}, {"EVENT Code", "EVENT Name"}),
#"Remove EVENT" = Table.RemoveColumns(#"Expand EVENT",{"EVENT"})

#"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

Dealing with Dimensions - Hooking Power BI up to Microsoft Dynamics NAV (and Dynamics 365 for Financials!) (linkedin.com)

Quick Links

Leaderboard