Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Junaid11
Helper V
Helper V

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:

Category _1Category_1_nameCategory _2Category_2_name
J-1ABCL-1YAB
 BAC KAN
 CAC MAJ

The second table is LLP and its data is:

Category _1Category_1_nameCategory _2Category_2_name
J-1BACP-1CAD
 KAL DAC
 ABC BAD

My Third table is REN and data is below:

Category _1Category_1_nameCategory _2Category_2_name
P-1DACL-1KAN
 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. 

CategoryName CombinedMAINLLPREN
J-1ABC11 
J-1BAC11 
J-1CAC1  
J-1KAL 1 
L-1YAB1  
L-1KAN1 1
L-1MAJ1  
L-1ABC  1
L-1LLC  1
P-1CAD 1 
P-1DAC 11
P-1BAD 11
P-1UAB  1

Your help would be quite beneficial.
Thank you

1 ACCEPTED SOLUTION
v-rzhou-msft
Community Support
Community Support

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:

RicoZhou_0-1668412648119.png

Finally, create a matrix to get result.

RicoZhou_1-1668412681326.png

 

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.

 

 

View solution in original post

3 REPLIES 3
v-rzhou-msft
Community Support
Community Support

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:

RicoZhou_0-1668412648119.png

Finally, create a matrix to get result.

RicoZhou_1-1668412681326.png

 

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
Solution Sage
Solution Sage

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
Solution Sage
Solution Sage

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors