Compare three tables columns with appended table

[Junaid11]

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:

Category _1	Category_1_name	Category _2	Category_2_name
J-1	ABC	L-1	YAB
	BAC		KAN
	CAC		MAJ

The second table is LLP and its data is:

Category _1	Category_1_name	Category _2	Category_2_name
J-1	BAC	P-1	CAD
	KAL		DAC
	ABC		BAD

My Third table is REN and data is below:

Category _1	Category_1_name	Category _2	Category_2_name
P-1	DAC	L-1	KAN
	BAD		ABC
	UAB		LLC

I want the category of all the table to be shown in one column and category cname of all the tables in other column:
I want to check whether Category and category name in are same then create 3 columns is it was in Main and LLP and REN then put 1 in their respective cooumns.I wanted to find out if the same category and category name was in each file name then there should be put one its column name like category J-1 and category name ABC was in file Main as well as in LLP file so I put 1 in their columns while it was not in REN table so it has blank infront of it I have attached the outcome I wanted to have in below table. Please have a look and let me know is it possible to achieve. 

Category	Name Combined	MAIN	LLP	REN
J-1	ABC	1	1
J-1	BAC	1	1
J-1	CAC	1
J-1	KAL		1
L-1	YAB	1
L-1	KAN	1		1
L-1	MAJ	1
L-1	ABC			1
L-1	LLC			1
P-1	CAD		1
P-1	DAC		1	1
P-1	BAD		1	1
P-1	UAB			1

Your help would be quite beneficial.
Thank you

[v-rzhou-msft · Accepted solution]

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:

Finally, create a matrix to get result.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mangaus1111]

Hi @Junaid11 ,

please If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mangaus1111]

Hi @Junaid11 ,

see my pbi file

https://1drv.ms/u/s!Aj45jbu0mDVJi1DzqvSNf32010Gt?e=yuE4vl

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.