Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
I am trying to create a report based on an excel file that has multiple sheets of data combined in one. I am trying to create different headers from this data & separate it in a way that I can best visualize data from it.
Following is the raw data from which I am trying to create separate headers.
And, following is how I want the final data to be transformed and look like in Power BI. The color codes here are to identify the rows I am trying to transpose from the raw data.
I have tried removing columns, changing headers, transposing & using group by as well. Nothing seems to handle the issue. Also, I didn't find any related post on this seems like a common issue but no one seems to know the solution.
Any help is greatly appreciated.
Thanks.
-Niki
Some related content I found:
@edhans
https://community.powerbi.com/t5/Power-Query/Manually-Assign-Certain-Rows-as-Column-Headers/m-p/1018...
Solved! Go to Solution.
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.
If none of the above are true, is there some other convention to differentiate a Site name from a Model?
Hi, ronrsnfld
Both the assumptions are not true.
However, there is one way to separate Site Info that worked for me.
Next, I created the 'Models' column- by using the Fill down function from Transform. Now, I can filter out the fields that are not required from the visual side for this specific 'Model' column.
I Still need to figure out the rest of the columns- Date, Workdays, Units/day, Planned, Build
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
Proud to be a Super User!
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 |
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
69 | |
61 | |
18 | |
16 | |
13 |