Create list of accounts from NAV account schedules or COA totaling syntax

In this blogpost I’ll show you how to create a list of account numbers from the totalling syntax that you find in Dynamics NAV account schedules or chart of accounts for example:

1000..1090|1500|2000..2020

This string shall be transformed into a “real” list of account numbers in the query editor that can be used to select all accounts within those ranges.

  1. Split by pipe (“|”)

    • Text.Split(“1000..1090|1500|2000..2020”, “|”) splits the text into a list like so:

      NAV account schedules: 1) Split by pipe

  2. Split by dots (“..”)

    • In order to split each element of the list above by the 2 dots, I have to apply the split command to every item of the list. Therefore I use List.Transform like so:
      • List.Transform(SplitByPipe, each Text.Split(_, “..”)
    • This returns a nested list like below, with 2 list elements for those who contained a dotted range and just one element for the single ones like so:

      NAV account schedules: 2) Nested split by dots using List.Transform

  3. Create lists

    • In this step, the list of actual account number will be created. Again, I use List.Transform to transform the existing list by applying a function to each of its elements. The formulas required depend on the number of items in each list. If there is just one item in it (like the “1500” in the second row), nothing has to be done and the value can stay as it is (see yellow part in the pic below). But if the element contains 2 elements (like the “1000” and “1090” above), I create a list that starts from the first list element to the last. And as the elements in my list so far are in text-format, I have to convert them to number before when using them in the list creator ( {…} in green ):

      NAV account schedules: 3) Create list of accounts

  4. Flatten nested list of lists

  • Now that I have all numbers per list element, I just have to combine the 3 list into one. The function List.Combine does this like so:

    NAV account schedules: 4) Flatten nested list of list

Further steps

From here, you can convert that list into a table and merge it with your chart of accounts as an inner merge. That will eliminate all the numbers that have been created above who might actually not have a match in the accounts table.

Function

For convenience, I’ve converted these steps to a function that take the original string as the only mandatory parameter. There are 2 additional optional parameters that take different characters for the pipe and dots if you want to apply this logic to a syntax that used different signs:


let func =
(string as text, optional PipeReplacement as text, optional DotsReplacement as text) =>
let
Source = string,
Pipe = if PipeReplacement is null then "|" else PipeReplacement,
Dots = if DotsReplacement is null then ".." else DotsReplacement,
SplitByPipe = Text.Split(Source, Pipe),
SplitByDots = List.Transform(SplitByPipe, each Text.Split(_, Dots)),
CreateLists = List.Transform(SplitByDots, each
if List.Count(_) = 1
then {Number.From(_{0})}
else {Number.From(_{0})..Number.From(_{1})}
),
CombineLists = List.Combine(CreateLists)
in
CombineLists ,
documentation = [
Documentation.Name = " NAV.AccountListFromTotaling ",
Documentation.Description = " Creates a list of account numbers from the totalling syntax in NAV chart of accounts or account schemes. ",
Documentation.LongDescription = " Creates a list of account numbers from the totalling syntax in NAV chart of accounts or account schemes. Alternative delimiters can be used in the optional parameters. ",
Documentation.Category = " NAV ",
Documentation.Source = " https://wp.me/p6lgsG-Ye ",
Documentation.Version = " 1.0 ",
Documentation.Author = " Imke Feldmann: www.TheBIccountant.com: https://wp.me/p6lgsG-Ye . ",
Documentation.Examples = {[Description = " See this blogpost: https://wp.me/p6lgsG-Ye ",
Code = " NAVAccountListFromTotaling(""1000..1005|1010|1020..1023"") ",
Result = " {""1000"", ""1001"", ""1002"", ""1003"", ""1004"", ""1005"", ""1010"", ""1020"", ""1021"", ""1022"", ""1023""} "]}]
in
Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))

view raw

NAV.AccountListFromTotaling.pq

hosted with ❤ by GitHub

Enjoy & stay queryious 😉

Comments (10) Write a comment

  1. Pingback: #BIPowerHour, Tips, Slicer pane, Power BI Desktop and Gateway updates, more... (October 29, 2018) | Guy in a Cube

  2. If D is the Name of a Table and containing a single Column called Code then the following M Code works fine

    let
    Source = Excel.CurrentWorkbook(){[Name=”D”]}[Content],
    mSplitColDLPipe = Table.ExpandListColumn(Table.TransformColumns(Source, {{“CODE”, Splitter.SplitTextByDelimiter(“|”, QuoteStyle.Csv)}}), “CODE”),
    mSplitColDLDot = Table.ExpandListColumn(Table.TransformColumns(mSplitColDLPipe,{{“CODE”, each try {Number.From(Text.BeforeDelimiter(,”..”))..Number.From(Text.AfterDelimiter(,”..”))} otherwise {_}, type list}}),”CODE”),
    mChgType = Table.TransformColumnTypes(mSplitColDLDot,{{“CODE”, type number}})
    in
    mChgType

    Reply

  3. Take this one a shot!…lol….

    = Expression.Evaluate(“{“&Text.Replace(“1000..1090|1500|2000..2020″,”|”,”,”)&”}”)

    Reply

    • What a nice catch John!
      Never occurred to me to use Expression.Evaluate like this & I actually had deleted this from my list, because using the #shared environment doesn’t work in the service.
      But using it this way works without an environment-record. Love it!!
      /Imke

      Reply

  4. Hi, Imke, so glad you love the solution.

    Speaking of which, I got some other usages of Expression.Evaluate that I learned from Mr Huang CaiGuang, a very smart guy from China who always has some useful m-code, say, the 5th parameter of Table.Group, the Regular Expression in PQ by using JavaScript…ring a bell?…lol….

    Ok, back to Expression.Evaluate….below listed M-code for your information:

    1\ = Expression.Evaluate(“List.Sum({1..5})”,#shared)
    2\ = Expression.Evaluate(“List.Sum({1..5})”,[List.Sum=List.Sum])
    3\ = let a={1..5} in Expression.Evaluate(“a”,[a=a])
    4\ = let a={1..5} in Expression.Evaluate(“List.Sum(a)”,[a=a,List.Sum=List.Sum])
    5\ = let a={1..5} in Expression.Evaluate(“List.Sum(a)”,[a=a]&#shared)
    6\ = Expression.Evaluate(“[“&”a=List.Sum({1..5}),b=List.Average({1..5})”&”]”,#shared)

    you may test it in PQ, hope it’ll help to take Expression.Evaluate back to your M-List…lol…

    Merci.

    Reply

  5. Hello Imke,
    Great article and many thanks because I’m using it a lot in my reports. 🙂

    I have two questions – if possible:
    1. is there a way to create something similar for accounts “beginning with…” like this “51|53”
    2. is there a way to create a range for “text based accounts” like “704-01..704-05”

    Reply

Leave a Reply