Power Query Join tables

I think that this should work:

{[Totaling.1]..[Totaling.2]}

This will give you a list of numbers incrementing by 1. So probably containing numbers where no account no exists. That's when JoinKind.Inner comes into play in order to reduce the list to the existing account no only.

Here's another thought, what if you treat this as a LOOKUP problem? I have some examples in this blog article at the bottom where no relationship is required between tables and such:

http://community.powerbi.com/t5/Community-Blog/Good-Ol-VLOOKUP-The-Ultimate-Guide-to-Lookups-in-Power-BI/ba-p/35912

Yes, instead of joining on fields/columns which requires exact matches one can use the technique described here: http://community.powerbi.com/t5/Desktop/Complex-PowerQuery-Merge-query-using-substrings/m-p/45582#M17717

It's basically a crossjoin and then filter on what you need. So you might experience some performance-issues. ... But after having a look at your code it seems as if you're dealing with dimension tables only, so not too serious probably. And you've reduced the number of columns already - good. So just make sure to use the buffer on the table that will be passed into the crossjoin.

An alternative approach would be to create a list of numbers {Totalling.1..Totalling.2}, expand and then perform a JoinKind.Inner.

What you're doing here looks like a decomposition of the NAV account-schedules, am I right? So it would be preferrable to have a dynamic list of column names in the split with the "|". This can be accomplished by simply omitting the specific definition of the column-names like this:

Source2SplitByDelimiter = Table.SplitColumn(
                Source2,
                "Totaling",
                Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv)
            ),

ImkeF: You are so right... I am creating a table to be used in a many-to-many relationship between my general ledger transaction and general ledger account dimension, so I can take advantage of NAV totaling column to create subtotals in my account dimension. This is how I normaly solve this when creating SSAS multi-dimentional cubes.

Thank you for your suggestion on omitting the specific definition of the column names in the Split by delimiter step.

My initial idea was to create a list from the 2 columns but I can't seem to find the right function to do this. Do you have any ideas?

I am not sure how I should use your crossjoin solution since I don't always know all the values to look up until I have created a list with all posible values between Totaling.1 and Totaling.2?

Adding something like this will give me exactly what I need to make my final join, but how do I replace the values 1120 and 1130 with Totaling.1 and Totaling.2?

    TotalList = Table.AddColumn ( Source2ReplaceNulls, "TotalList", each {1120..1130} ),
    ExpandedTotalList = Table.ExpandListColumn(TotalList, "TotalList")
in
    ExpandedTotalList

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.