Dynamics NAV account schedule

[modeco]

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?

[v-shex-msft · Accepted solution]

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

[Anonymous]

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. 

[clausm73]

The formula is still there - in the formula widget - it can be expanded on my mobile browser. But somehow my desktop browser is not able to expand and show the formula inside the widget - I guess its because of JavaScripts on the page is being blocked. Anyways - a similar solution - and according to a colleague of mine - without error in some circumstances (unfortunately the details about the error is lost to me?!) - has been made by Imke Feldmann on her blog site - and its awesome: https://www.thebiccountant.com/2018/10/27/create-list-of-accounts-from-nav-account-schedules-totalli...

[clausm73]

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!

[benovic]

... did you crack that nut ?

[Yggdrasill]

... did you crack that nut ?