Power Query Join tables

ImkeF thank you that was the solution. I had already tried that but received an error, but that was because I also needed to change type on Totaling.1 and Totaling.2 to numbers - this could be a problem since the source column in NAV is an nvarchar column that can have values that can't be converted into a number. I will need to figure out a way to handle this, either by excluding those from my code or perhaps create a surrugate key instead.

I will continue working on this and get back with my final code.

Thank you for all you help so far.

Good point - as it gave me the idea for a better solution: Instead of a surrogate key, create an index on your (sorted) chart of accounts (on accounts only). This way the increment by 1 will not produce unnecessary numbers where no account matches.

Unfortunately the "trick" with omitting the column names doesn't work - sorry. Instead we can create a dynamic one. You'll find it in steps "NoOfColumns" and "ColumnNames":

let
    ChartOfAccounts = #table({"AccountNo", "AccountType"}, {{"K100", "Account"}, {"200", "Account"}, {"K100|200", "Totalling1"}, {"300", "Account"}, {"2000", "Account"},{"3000", "Account"}, {"4000", "Account"}, {"2000..4000", "Totalling2"}, {"5000", "Account"}, {"6000", "Account"}, {"8000", "Account"}, {"9000", "Account"}, {"2000..4000|200|5000..9000", "Totalling3"}}),
    #"Filtered Rows" = Table.SelectRows(ChartOfAccounts, each ([AccountType] = "Account")),
    Indexed = Table.AddIndexColumn(#"Filtered Rows", "Index", 1, 1),
    #"Merged Queries" = Table.NestedJoin(ChartOfAccounts,{"AccountNo"},Indexed,{"AccountNo"},"NewColumn",JoinKind.LeftOuter),
    IndexedCoA = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Index"}, {"Index"}),
    #"Filtered Rows1" = Table.SelectRows(IndexedCoA, each (Text.StartsWith([AccountType], "Totalling"))),
    NoOfColumns = Table.AddColumn(#"Filtered Rows1", "NumberOfColumns", each List.Count(Text.PositionOf([AccountNo], "|", Occurrence.All))+1),
    ColumnNames = List.Transform({1..List.Max(NoOfColumns[NumberOfColumns])}, each "Column"&Text.From(_)),
    #"SplitColumn|" = Table.SplitColumn(NoOfColumns,"AccountNo",Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), ColumnNames),
    #"Removed Columns" = Table.RemoveColumns(#"SplitColumn|",{"Index", "NumberOfColumns"}),
    UpivotColumns = Table.UnpivotOtherColumns(#"Removed Columns", {"AccountType"}, "Attribute", "Value"),
    #"Removed Columns1" = Table.RemoveColumns(UpivotColumns,{"Attribute"}),
    #"SplitColumn.." = Table.SplitColumn(#"Removed Columns1","Value",Splitter.SplitTextByDelimiter("..", QuoteStyle.Csv),{"Totalling.1", "Totalling.2"}),
    #"Added Index" = Table.AddIndexColumn(#"SplitColumn..", "Index", 0, 1),
    IndexedAccountType = Table.AddColumn(#"Added Index", "IndexedAccountType", each Text.Combine({[AccountType], Text.From([Index], "de-DE")}, "--"), type text),
    #"Removed Columns3" = Table.RemoveColumns(IndexedAccountType,{"AccountType", "Index"}),
    AllAccountsInOneColumn = Table.UnpivotOtherColumns(#"Removed Columns3", {"IndexedAccountType"}, "Attribute", "Value"),
    LookupAccountIndex = Table.NestedJoin(AllAccountsInOneColumn,{"Value"},IndexedCoA,{"AccountNo"},"NewColumn",JoinKind.LeftOuter),
    ShowAccountIndex = Table.ExpandTableColumn(LookupAccountIndex, "NewColumn", {"Index"}, {"Index"}),
    #"Removed Columns2" = Table.RemoveColumns(ShowAccountIndex,{"Value"}),
    PivotBackTo2Columns = Table.Pivot(#"Removed Columns2", List.Distinct(#"Removed Columns2"[Attribute]), "Attribute", "Index"),
    ListOfIndices = Table.AddColumn(PivotBackTo2Columns, "AccountIndices", each if [Totalling.2]=null then {[Totalling.1]} else {[Totalling.1]..[Totalling.2]}),
    #"Expanded AccountIndices" = Table.ExpandListColumn(ListOfIndices, "AccountIndices"),
    LookupAccountNo = Table.NestedJoin(#"Expanded AccountIndices",{"AccountIndices"},IndexedCoA,{"Index"},"NewColumn",JoinKind.LeftOuter),
    ShowAccountNo = Table.ExpandTableColumn(LookupAccountNo, "NewColumn", {"AccountNo"}, {"AccountNo"}),
    CleanUpTotalling = Table.SplitColumn(ShowAccountNo,"IndexedAccountType",Splitter.SplitTextByEachDelimiter({"--"}, QuoteStyle.Csv, true),{"IndexedAccountType.1", "IndexedAccountType.2"}),
    #"Removed Other Columns" = Table.SelectColumns(CleanUpTotalling,{"IndexedAccountType.1", "AccountNo"}),
    #"Sorted Rows" = Table.Sort(#"Removed Other Columns",{{"IndexedAccountType.1", Order.Ascending}})
in
    #"Sorted Rows"

This is a full code incl. sample data which you can copy into your advanced editor and follow the steps along. But don't use Internet Explorer, because that's buggy and will insert non-printable characters or like which will cause strange error-messages!

Great - that looks neat and clean :-)

Yes, our solutions are quite similar - you managed with one pivoting-less.

But watch out: Omitting the column names will only work if the Totalling with the most |-signs sits in the first row of your table!

Thank you for that last tip, I will have to handle that.