Forum Discussion

modeco's avatar
modeco
Frequent Visitor
8 years ago
Solved

Dynamics NAV account schedule

I need to create a Power BI Finance report with data from our Dynamics NAV system. The goal is to use Account Schedule table from Dynamics NAV where GL account grouping is set up. The setup consists of two main columns “Group name” and “GL accounts” which contains account filter for accounts that are included in the group. For example, we have a group “operational expenses” containing such account filter: 7501..7508|7124|7130 (meaning that the group includes all accounts from 7501 until 7508 and 7124 and 7130).

There are many groups and each group may have diferent range of accounts.

Two dots “..” means “from-to” connection, and vertical line “|” means OR.

From my understanding I need is to transform this setup into such format:

 

Group name

GL accounts

operational expenses

7501

operational expenses

7502

operational expenses

7503

operational expenses

7504

operational expenses

7505

operational expenses

7506

operational expenses

7507

operational expenses

7508

operational expenses

7124

operational expenses

7130

 

Is it possible to get this done in query mode or using DAX? Can anyone give a hint on how to get this done?

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    8 years ago

    Hi modeco,

     

    Please refer to below formula to analysis custom filter formula:

    Spoiler
    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjc1MNTTA5IWNeaGRiZAwthAKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Fitler = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Fitler", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each 
Table.AddColumn(Table.ExpandListColumn(Table.AddColumn(Table.FromList(Text.Split([Fitler],"|"), Splitter.SplitByNothing(),{"List"}, null, ExtraValues.Error), "Account", each if Text.Contains([List],"..") 
then List.Numbers(Number.From(Text.Split([List],".."){0}),Number.From(Text.Split([List],".."){1})- Number.From(Text.Split([List],".."){0})+1) else null), "Account"), "Result", each if [Account] <> null then [Account] else [List])[Result]),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", Int64.Type}})
in
    #"Changed Type1"

    Result:

     

    Regards,
    Xiaoxin Sheng

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi modeco,

     

    Please refer to below formula to analysis custom filter formula:

    Spoiler
    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjc1MNTTA5IWNeaGRiZAwthAKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Fitler = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Fitler", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each 
Table.AddColumn(Table.ExpandListColumn(Table.AddColumn(Table.FromList(Text.Split([Fitler],"|"), Splitter.SplitByNothing(),{"List"}, null, ExtraValues.Error), "Account", each if Text.Contains([List],"..") 
then List.Numbers(Number.From(Text.Split([List],".."){0}),Number.From(Text.Split([List],".."){1})- Number.From(Text.Split([List],".."){0})+1) else null), "Account"), "Result", each if [Account] <> null then [Account] else [List])[Result]),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", Int64.Type}})
in
    #"Changed Type1"

    Result:

     

    Regards,
    Xiaoxin Sheng

    • clausm73's avatar
      clausm73
      Icon for Advocate II rankAdvocate II
      7 years ago

      This is absolutely brilliant - nothing less! Have been searching the net for months now for this elegant solution (my brain just couldn't crack the nut).

       

      Now I just have to figure out howe to deal with the additive codes that occurs in the [Totaling] column for [Totaling Type] = 2 - that has to be handled as a parent-child hierarchy (multiple parents?) - and take into account how to deal with subtraction (child members with a "unary operator").

       

      Thank you, sir!

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi v-shex-msft, can you post the solution again - how you managed to split the | into rows.

      The solution formula seems to be gone.