Forum Discussion
Power Query copy columns to new table
In Excel Power Query i have a table has columns of data that i need to stack into a new table.
Below is the original table.
Table that need to be transformed
I Need a new table that copies that data column in
Columns P6 Activity ID, Bridge #, P6 Discipline Code, Description, Resource 1, Quantity 1 and puts into a new table.
Then copies that data column in
Columns P6 Activity ID, Bridge #, P6 Discipline Code, Description, Resource 2, Quantity 2 and put it below the data copied above
Then copies that data column in
Columns P6 Activity ID, Bridge #, P6 Discipline Code, Description, Resource 3, Quantity 3 and put it below the data copied above
Then copies that data column in
Columns P6 Activity ID, Bridge #, P6 Discipline Code, Description, Resource 4, Quantity 4 and put it below the data copied above
Then copies that data column in
Columns P6 Activity ID, Bridge #, P6 Discipline Code, Description, Resource 4, Quantity 4 and put it below the data copied above..
Does anyone one how to do this in Power Query?
Thanks For the Help
Hi Jcramb
Use Edit queries in Power BI Desktop
For this test data
NUM ID Bridge p6 description duration bud mahour forecast resource1 quantity1 resource2 quantity2 resource3 quantity3 1 1 a 1 as 1 1 1 a1 aa1 a2 aa2 a3 aa3 2 2 b 2 bd 1 1 1 b1 bb1 b2 bb2 b3 bb3 3 3 c 3 ce 1 1 1 c1 cc1 c2 cc2 c3 cc3 4 4 d 4 dw 1 1 1 d1 dd1 d2 dd2 d3 dd3 1.select columns from "Resource 1", "Quantity 1" to "Resource 3", "Quantity 3",
then select "Unpivot columns"
after this step, it generate new columns "Attribute" and "Value"
2.split column "Attribute" to two columns "Attribute.1" and "Attribute.2"
3.select "Attribute.1" and "Value: columns, then select "Pivot columns"
4.remove columns you don't need
finally, i get table as below
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hi Jcramb
It will take a long time to reproduce your data, and i'm not clear with the result table.
Could you copy the data from excel and paste here?
Also, enter column header and some example data in excel to show the expected result table?
Best Regards
Maggie
Hi Jcramb
Use Edit queries in Power BI Desktop
For this test data
NUM ID Bridge p6 description duration bud mahour forecast resource1 quantity1 resource2 quantity2 resource3 quantity3 1 1 a 1 as 1 1 1 a1 aa1 a2 aa2 a3 aa3 2 2 b 2 bd 1 1 1 b1 bb1 b2 bb2 b3 bb3 3 3 c 3 ce 1 1 1 c1 cc1 c2 cc2 c3 cc3 4 4 d 4 dw 1 1 1 d1 dd1 d2 dd2 d3 dd3 1.select columns from "Resource 1", "Quantity 1" to "Resource 3", "Quantity 3",
then select "Unpivot columns"
after this step, it generate new columns "Attribute" and "Value"
2.split column "Attribute" to two columns "Attribute.1" and "Attribute.2"
3.select "Attribute.1" and "Value: columns, then select "Pivot columns"
4.remove columns you don't need
finally, i get table as below
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hi Jcramb
Is this problem sloved?
If not, please let me know.
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.