Compare three tables columns with appended table

Hello, I have three tables and I want them to be shown in one table with 3 new columns created from them. The 3 tables are named as below with below data: This table name is main and its data is: ...

Need Help

Anonymous

3 years ago

Hi Junaid11 ,

In mangaus1111's workaround, he create three duplicate tables to achieve your goal. You can try my way to create an Append table directly.

Firstly, add [TableName] columns in three tables. Then Append and do some transform.

let
    Source = Table.Combine({Main, LLP, REN}),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Category_1_name", "Category_2_name", "TableName", "Index"}, "Attribute", "Value"),
    #"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Unpivoted Columns", {"TableName", "Index", "Attribute", "Value"}, "Attribute.1", "Value.1"),
    #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Columns1","_name","",Replacer.ReplaceText,{"Attribute.1"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value"," ","",Replacer.ReplaceText,{"Attribute"}),
    #"Filtered Rows" = Table.SelectRows(#"Replaced Value1", each ([Attribute.1] = [Attribute])),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Value", "Category"}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns",{"Attribute.1"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns1",{{"Value.1", "Name"}}),
    #"Added Conditional Column" = Table.AddColumn(#"Renamed Columns1", "TableSort", each if [TableName] = "Main" then 1 else if [TableName] = "LLP" then 2 else 3)
in
    #"Added Conditional Column"

Append table: