Forum Discussion
Creating separate columns & transposing data using Power Query
I have a solution and I posted it along with a long-winded explanation. For some reason, that and the code has disappeared. So here is a link to the file on my OneDrive. There should be enough comments in the code for you to follow things along. Separate and Transpose
Here is the screenshot of the data I worked from and the results.
Note that when I created a "Table" from the data range, I selected that the table did NOT include headers.
Hi nik751
I'd love to have a go at solving this for you but without some sample data I don't want to spend ages recreating what is in your image. Plus doing that can lead to errors if I recreate the data/layout incorrectly.
Regards
Phil
Hi Phil,
I am pasting the raw data & expected data after transformation (in Power BI) here. Hope it helps.
Thanks.
Raw Data in Excel:
Site-ABC |
| Jan-21 | Feb-21 |
| WorkDays | 20 | 19 |
| Units/day | 2 | 2 |
Model 1 | Planned | 5 | 2 |
| Built | 3 | 1 |
Model 2 | Planned | 6 | 17 |
| Built | 2 | 10 |
|
|
|
|
Site-XYZ |
| Jan | Feb |
| Work Days | 20 | 19 |
| Units/day | 1 | 1 |
Model 1 | Planned | 5 | 6 |
| Built | 4 | 5 |
Model 2 | Planned | 10 | 5 |
| Built | 5 | 2 |
Expected Output in Power BI(after transformation):
Site | Date | Workdays | Units/day | Models | Planned | Build |
ABC | Jan-21 | 20 | 2 | Model1 | 5 | 3 |
ABC | Jan-21 | 20 | 2 | Model2 | 6 | 2 |
XYZ | Jan-21 | 20 | 1 | Model1 | 5 | 4 |
XYZ | Jan-21 | 20 | 1 | Model2 | 10 | 5 |
ABC | Feb-21 | 19 | 2 | Model1 | 2 | 1 |
ABC | Feb-21 | 19 | 2 | Model2 | 17 | 10 |
XYZ | Feb-21 | 19 | 1 | Model 1 | 6 | 5 |
XYZ | Feb-21 | 19 | 1 | Model2 | 5 | 2 |