Forum Discussion
Merging two data tables by creating the columns from second table dates.
Hello Dear professionals!
I have a data in Power Query from Sql.
UniqueID Name Amount Type (then I have a Table "br_Data")
1 Y.K. 10 L Table
2 T.R. 20 M Table
3 M.C. 85 A Table
4 H.L. 88 S Table
...
999
When I press on Table column, say first row, this appears:
UniqueID Source Year Period Persons Size Colour ...
1 B 2004 1 34 LD 43r
1 C 2004 2 65 GR 66h
1 F 2004 3 74 RF iy7
... ... ... ... ... ... ...
999 .. 2020 7 88 .. ...
So my goal is to get the first table with the Year, Period, Persons and Size from second table.
It should look like this:
UniqueID Name Amount Type 2004 _1 2004_2 2004_3 ... 2020_7 2004_1_Size 2004_2_Size 2004_3_Size
1 Y.K. 10 L 34 65 74 88 LD GR RF
2 T.R. 20 M ... ... ... ... ... ... ...
3 M.C. 85 A ... ... ... ... ... ... ...
4 H.L. 88 S ... ... ... ... ... ... ...
So the Year, Period and Size from second table should be a column name for that particular UniqueID
I hope that someone can help me with this case.
Thank's
Try
1- Duplicate your table.
2- In new table remove all rows other that "Table"
3- Add new column ("Year-Period") that will Concatenate "Year" & Period
4- Remove all columns except "Unique ID" "Year-Period" "Persons"
5- Pivot this table accordingly
Above will create your 2nd table having unique values against each ID
6- Now From orignal table delete "Table" column
7- Merge this table with above table based on "UniqueId" and hopefully you will have your results
4 Replies
Try
1- Duplicate your table.
2- In new table remove all rows other that "Table"
3- Add new column ("Year-Period") that will Concatenate "Year" & Period
4- Remove all columns except "Unique ID" "Year-Period" "Persons"
5- Pivot this table accordingly
Above will create your 2nd table having unique values against each ID
6- Now From orignal table delete "Table" column
7- Merge this table with above table based on "UniqueId" and hopefully you will have your results
Thanks for the idea, I got exactly what I planned
Seems like you want to pivot those columns. Hard to say. In general though, you generally do not want the data structure that you specify but you might have an exception.
Hi M_Dmitry ,
This is doable, but do you really want to make your structure so much complicated? Processing and maintenance-wise this is a quite inefficient data structure - unless this is your final stop for a one-off exercise (after which you plan to copy it to Excel).
Are you sure you do not want to expand all tables (expand the column) to get something like this:
UniqueId Name Amount Type Year Period Persons Size 1 Y.K. 10 L 2004 1 34 LD 1 Y.K. 10 L 2004 2 65 GR 1 Y.K. 10 L 2004 3 74 RF 1 Y.K. 10 L … … … … 1 Y.K. 10 L 2020 7 88 … Kind regards,
JB
