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

     

sdjensen's avatar
sdjensen
Solution Sage
10 years ago

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?

sdjensen's avatar
sdjensen
Solution Sage
10 years ago

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