Forum Discussion
Helper VCompare 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 _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
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 ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
- mangaus1111
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.
- 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.
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 ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
