Compare columns in 2 the same structured tables.

[jaryszek]

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),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Key", type text}, {"Col2", type text}, {"Col3", type text}, {"TableMap", type any}}),
#"Expanded TableMap" = Table.ExpandTableColumn(#"Changed Type", "TableMap", {"Col2", "Col3"}, {"TableMap.Col2", "TableMap.Col3"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded TableMap",{{"TableMap.Col2", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "CustomCol3", each if Text.Contains([TableMap.Col3], "Name_") then [Col3] else [TableMap.Col3]),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "CustomCol2", each if Text.Contains([TableMap.Col2], "Name_") then [Col2] else [TableMap.Col2]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"TableMap.Col2", "TableMap.Col3"}),
#"Merged Columns" = Table.CombineColumns(#"Removed Columns",{"Col2", "Col3"},Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"MergedDef"),
#"Inserted Merged Column" = Table.AddColumn(#"Merged Columns", "MergedMap", each Text.Combine({ [CustomCol2], [CustomCol3]}, ";"), type text),
#"Removed Columns1" = Table.RemoveColumns(#"Inserted Merged Column",{"CustomCol3", "CustomCol2"}),
#"Added Custom2" = Table.AddColumn(#"Removed Columns1", "Custom", each if [MergedDef] <> [MergedMap] then 1 else 0),
#"Filtered Rows" = Table.SelectRows(#"Added Custom2", each true),
#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [Custom] = 1)
in
#"Filtered Rows1"

Idea is to compare TableDefaults (TableDef) with TableMap which can be customized by user.
If one row in specific column is changed - i should show entire row as result.

So how i am building it?

1) merging queries by Key using Key Column
2) Added custom columns for all other columns to check if there is a string pattern "Name_To_Skip". If there is replace value from tableDef column into tableMap.
3) Removing not necessary columns
4) Merging columns from TableDef and TableMap and comparing if TableMap was customized.
5) If yes add custom column = 1, if not = 0
6) filter rows for only equals = 1

Imagine that i have about 30 tables to set up.
Plus each of it has about 2-15 columns to check.

What would be awesome is function which:
Check if string "Name_To_Skip" exists in expanded columns for each column and replace with equivalent in source column.
Like TableDef [Col2] and [TableMap.Col3] check them and replace adequately.

Workbook attached on my google drive:

https://drive.google.com/file/d/1zBdbWP-FNcX-Zw5HugcF_EU2m9oc_5w-/view?usp=sharing

Please help,
i do not know if this is too difficult to write,
Jacek

[jaryszek]

Thank you so much! 

Jacek

[v-jingzhang · Accepted solution]

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.

[jaryszek]

Anyone can help?

Best,
Jacek

[v-jingzhang]

Hi @jaryszek 

Does my reply solve your problem? If yes, kindly accept it as the solution. Otherwise, please provide more details about your problem so that we can work on it further. Thanks.

Regards,
Jing

[edhans]

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]

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