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

I would prefer a solution in "M" for 2 reasons. 1. I like to keep all transformations in "M", so I always only have one place to search for them, 2. I need to apply the result to another table. I know I will properly be able to solve this in DAX, but I challenged myself to do it with "M", but this step gives me some problems.

 

EDIT: The source is SQL, but I have no control over the source and can't make or request changes to the source.

sdjensen's avatar
sdjensen
Solution Sage
10 years ago

So far the code to the new table I am creating is this:

let
    // 1 til 1 relation for konti af type "konto"
    Source = Table.SelectColumns( Table.SelectRows( Finanskonto, each [FinanskontoTypeSort] = 0 ), {"FinanskontoKey"} ),
    AddFinansKontoKeyIncluded = Table.AddColumn(Source, "FinanskontoKeyIncluded", 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", "Totaling"}  ),
    Source2SplitByDelimiter = Table.SplitColumn(
                Source2,
                "Totaling",
                Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv),
                {"Totaling.1", "Totaling.2", "Totaling.3", "Totaling.4", "Totaling.5", "Totaling.6", "Totaling.7", "Totaling.8", "Totaling.9", "Totaling.10"}
            ),
    Source2UnpivotedColumns = Table.UnpivotOtherColumns(
                Source2SplitByDelimiter, 
                {"FinanskontoKey"}, "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"})

in
    Source2ReplaceNulls
  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    10 years ago

    Just to throw this out there, but this seems like it would be a relatively simple SQL query to conditionally join the tables in a SELECT statement (at least easier than in "M" but it is an interesting problem to solve so I'll take a look although someone like ImkeF might be able to bang out the solution from memory.

    • sdjensen's avatar
      sdjensen
      Solution Sage
      10 years ago

      It would be very easy in SQL if the tables was like that in the source. However the Table2 I pasted is a result of the transformations I have posted earlier with splits and unpivots, so it's not a direct table from my source. The source is what I refer to in my query as "Finanskonto" and the 3 columns I pasted as table 1 is from this table.