NAV.AccountListFromTotaling.pq

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))