Forum Discussion

jaryszek's avatar
jaryszek
Super User
5 years ago
Solved

Compare columns in 2 the same structured tables.

Hi Guys, i need help with writing function to skip some manual steps while building query:   let Source = Table.NestedJoin(TableDef, {"Key"}, TableMap, {"Key"}, "TableMap", JoinKind.LeftOuter), ...
Need Help
  • v-jingzhang's avatar
    v-jingzhang
    5 years ago

    Hi jaryszek 

     

    I think I made it! You could try below codes to create a function. Download the attachment for details.

     

    (mapTable as table, defTable as table) =>
let
    // mapTable = TAbleMap,
    // defTable = TableDef,
    //Please keep the mapTable before the defTable in below Source step
    Source = Table.Combine({mapTable, defTable}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"TableName", "Key"}, "Attribute", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"TableName", type text}, {"Key", type text}, {"Attribute", type text}, {"Value", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","Name_To_Skip",null,Replacer.ReplaceValue,{"Value"}),
    #"Grouped Rows" = Table.Group(#"Replaced Value", {"Key", "Attribute"}, {{"Values", each _, type table [TableName=nullable text, Key=nullable text, Attribute=nullable text, Value=nullable text]}}),
    #"Expanded Values" = Table.ExpandTableColumn(#"Grouped Rows", "Values", {"TableName", "Value"}, {"TableName", "Value"}),
    #"Filled Up" = Table.FillUp(#"Expanded Values",{"Value"}),
    #"Removed Duplicates" = Table.Distinct(#"Filled Up", {"Key", "Attribute", "Value"}),
    hasDifferences = List.Contains(Table.Column(#"Removed Duplicates", "TableName"), "TableDef"),
    #"Filtered Rows" = Table.SelectRows(#"Removed Duplicates", each ([TableName] = "TableMap")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"TableName"}),
    #"Filtered Rows1" = Table.SelectRows(#"Removed Columns", each ([Key] = "cat")),
    #"Pivoted Column" = Table.Pivot(#"Filtered Rows1", List.Distinct(#"Filtered Rows1"[Attribute]), "Attribute", "Value"),
    table1 = #"Pivoted Column",
    table2 = #table(
    {
      "Key",   // First Column Field Name
      "Col"   // Second Column Field Name
    },
    {}
  )
in 
    if hasDifferences then table1 else table2

     

     

    Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as the solution to help other members find it.

edhans's avatar
edhans
Community Champion
5 years ago

I am having a bit of trouble understanding what you are wanting. Can you show us what you have and what the expected output is and the logic of how to do that? Use screenshots from Excel if you want. I have your file so I have data, but I am not sure what your goal is.

jaryszek's avatar
jaryszek
Super User
5 years ago

Thank you edhans. 

I will provide 2 examples:

1) When TableMap has not any rows customized by user:

 

TableMap:

 

TableDef - the same as TableMapbut instead of Name_To_Skip has value = 3

 

Fields are the same without first rowin Col3. We have string there "Name_To_Skip". 

If the string exists just replaced with value from table TableDef (=3) and tableDef after replacing compare with TableMap they are the sameso result will be: Empty output table (with no rows):

 

 

 

I will get empty table because all rows are the same - without any customizations. 


Sorry, i updated ExcelFile on my google drive - i had little error in output query, please use the newest one. 

Please download once again from my first post. 


2) User changed 2nd row in Col2, so "dog" is now "mouse":

 

Result will be:

 

"Cat" is our Key. So Query checked if for the Key Cat any rows were changed. In this case "dog" was changed to "mouse" so i see row with difference. 

let me know if you understand it.


Best,
Jacek