Forum Discussion

robertpisarek's avatar
robertpisarek
Regular Visitor
8 years ago
Solved

Merge in Power Query based on conditions

How to merge the same table based on two conditions in PowerQuery?   For example in SQL:   select * from Table1 t1 inner join Table1 t2 on t1.TransactionID = t2.TransactionID and t1.CategoryID...
  • MarcelBeug's avatar
    MarcelBeug
    8 years ago

    My suggestion would be to join on TransactionID and remove the results where the CategoryID's are equal.
    As you require an inner join, this also means removing empty nested tables.

     

    let
        Source = Table.NestedJoin(Table1,{"TransactionID"},Table1,{"TransactionID"},"t2",JoinKind.Inner),
        FilteredOnCategory = Table.ReplaceValue(Source,each [t2],(Earlier) => Table.SelectRows(Earlier[t2],each [CategoryID] <> Earlier[CategoryID]), Replacer.ReplaceValue,{"t2"}),
        RestoredType = Value.ReplaceType(FilteredOnCategory,Value.Type(Source)),
        FilteredNoEmptyTables = Table.SelectRows(RestoredType, each not Table.IsEmpty([t2])),
        Expanded_t2 = Table.ExpandTableColumn(FilteredNoEmptyTables, "t2", {"CategoryID"}, {"t2.CategoryID"})
    in
        Expanded_t2

     

    The RestoredType step restores the column types from the Source table, as Table.ReplaceValues resets all column types to any.

MarcelBeug's avatar
MarcelBeug
Community Champion
8 years ago

My suggestion would be to join on TransactionID and remove the results where the CategoryID's are equal.
As you require an inner join, this also means removing empty nested tables.

 

let
    Source = Table.NestedJoin(Table1,{"TransactionID"},Table1,{"TransactionID"},"t2",JoinKind.Inner),
    FilteredOnCategory = Table.ReplaceValue(Source,each [t2],(Earlier) => Table.SelectRows(Earlier[t2],each [CategoryID] <> Earlier[CategoryID]), Replacer.ReplaceValue,{"t2"}),
    RestoredType = Value.ReplaceType(FilteredOnCategory,Value.Type(Source)),
    FilteredNoEmptyTables = Table.SelectRows(RestoredType, each not Table.IsEmpty([t2])),
    Expanded_t2 = Table.ExpandTableColumn(FilteredNoEmptyTables, "t2", {"CategoryID"}, {"t2.CategoryID"})
in
    Expanded_t2

 

The RestoredType step restores the column types from the Source table, as Table.ReplaceValues resets all column types to any.

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hello  MarcelBeug 

 

I am trying to wrap my head around the function you created for the 3rd argument NewValue inside Table.ReplaceValue()

Could you please explain how does this function work and especially the logic highlighted in red

 

 


FilteredOnCategory  
 = Table.ReplaceValue(// table, oldValue, NewValue, replacer as function, col to search. replace one value with another in the specified columns
     Source,     each [t2], // oldValue                     
     (Earlier) => Table.SelectRows(Earlier[t2],each [CategoryID] <> Earlier[CategoryID]), // NewValue 
     Replacer.ReplaceValue,{"t2"} // Replacer "t2" field name
 ),

  

Thanks