Forum Discussion

sdjensen's avatar
sdjensen
Solution Sage
10 years ago
Solved

Power Query Join tables

Hi,   I need some assistance with joining 2 tables in Query Editor. What I want is for each row in table 2 to return rows from table 1 where Table1.”Finanskonto Nummer” >= Table2.”Totaling.1” and T...
Need Help
  • sdjensen's avatar
    sdjensen
    10 years ago

    Okay I got a working solution now... thanks to ImkeF and Greg_Deckler for all their time and help.

     

    @ImkeF - it's strange that your trick doesn't work for you it worked for me? I would really like to accept your last code as a solution, but I haven't tested it, but I think our solutions are very close to each other.

     

    this is my final code:

    let
    // 1 til 1 relation for konti af type "konto"
    Source1 = Table.SelectColumns( Table.SelectRows( Finanskonto, each [FinanskontoTypeSort] = 0 ), {"FinanskontoKey"} ),
    Source1AddFinansKontoKeyIncluded = Table.AddColumn(Source1, "Included.FinanskontoKey", each [FinanskontoKey]), 
    
    // M til M relation for konti af type "sum" og "til-sum"
    Source2 = Table.SelectColumns( 
                Table.SelectRows( Finanskonto, each List.Contains({2, 4}, [FinanskontoTypeSort]) ), 
                {"FinanskontoKey", "Regnskab", "Finanskonto Nummer", "Totaling"}  
            ),
    Source2SplitByDelimiter = Table.SplitColumn(
                Source2,
                "Totaling",
                Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv)
            ),
    Source2UnpivotedColumns = Table.UnpivotOtherColumns(
                Source2SplitByDelimiter, 
                {"FinanskontoKey", "Regnskab", "Finanskonto Nummer"}, "Attribute", "Totaling"
            ),
    Source2SplitByDelimiter2  = Table.SplitColumn(
                Source2UnpivotedColumns,
                "Totaling",
                Splitter.SplitTextByDelimiter("..", QuoteStyle.Csv),
                {"Totaling.1", "Totaling.2"}
            ),
    Source2ReplaceNulls = Table.ReplaceValue(Source2SplitByDelimiter2, null, each _[Totaling.1], Replacer.ReplaceValue,{"Totaling.2"}
            ),
    Source2AddedTotaling1Idx = Table.Join(
                Source2ReplaceNulls,
                {"Regnskab", "Totaling.1"},
                Table.PrefixColumns(Table.SelectColumns( Finanskonto, {"Regnskab", "Finanskonto Nummer", "Index"} ), "Idx1"),
                {"Idx1.Regnskab", "Idx1.Finanskonto Nummer"}     
            ),
    Source2AddedTotaling2Idx = Table.Join(
                Source2AddedTotaling1Idx,
                {"Regnskab", "Totaling.2"},
                Table.PrefixColumns(Table.SelectColumns( Finanskonto, {"Regnskab", "Finanskonto Nummer", "Index"} ), "Idx2"),
                {"Idx2.Regnskab", "Idx2.Finanskonto Nummer"}     
            ),
    Source2TotalingList = Table.AddColumn ( Source2AddedTotaling2Idx, "TotalingList", each {[Idx1.Index]..[Idx2.Index]} ),
    Source2ExpandedTotalList = Table.ExpandListColumn(Source2TotalingList, "TotalingList"),
    Source2TableJoin = Table.Join( 
                Source2ExpandedTotalList, 
                {"Regnskab", "TotalingList"},
                Table.PrefixColumns( Table.SelectColumns(Finanskonto, {"Regnskab", "Index", "FinanskontoKey"} ), "Included"),
                {"Included.Regnskab", "Included.Index"}
            ),    
    Source2RemoveOtherColumns = Table.SelectColumns(Source2TableJoin,{"FinanskontoKey", "Included.FinanskontoKey"}),
    
    CombineSource1and2 = Table.Combine({Source1AddFinansKontoKeyIncluded, Source2RemoveOtherColumns})
in
    CombineSource1and2

     

ImkeF's avatar
ImkeF
Community Champion
10 years ago

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