Forum Discussion
Power Query to generate 3 columns based on 3 tables
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 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 ,
I made a sample file for you. Please download it for details.
The main idea is to use the Append function to append the category column and the name column.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Junaid11 What is the logic behind the last 3 columns? I can't understand because of mistakes in the post "I want to check whether Category and category name in are same"
Hello AntrikshSharma ,
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 itHi Junaid11 ,
I made a sample file for you. Please download it for details.
The main idea is to use the Append function to append the category column and the name column.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
