The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Dear all
Is it possible to transform an Excel sheet with multiple headers to a usable data table by only using existing feature (such as unpivot) in "transform"? (without any coding)
The first table is what I got now and the second one is the result I am hoping to have.
Many thanks!
Everyone can Buy | 18 or more | ||||||||
Ice Cream | Chips | Pizza | Beer | ||||||
2019 | Jan | $ 23.7 | $ 47.5 | $ 52.2 | $ 15.7 | ||||
Feb | $ 20.5 | $ 41.1 | $ 45.2 | $ 13.5 | |||||
Mar | $ 34.2 | $ 68.4 | $ 75.3 | $ 22.6 | |||||
Jun | $ 43.4 | $ 86.8 | $ 95.5 | $ 28.6 | |||||
July | $ 47.3 | $ 94.6 | $ 104.1 | $ 31.2 | |||||
Nov | $ 19.6 | $ 39.2 | $ 43.1 | $ 12.9 | |||||
Dec | $ 26.4 | $ 52.8 | $ 58.1 | $ 17.4 |
2009 | Jan | Every One can Buy | Ice Cream | 23.7 | |||||
2009 | Feb | Every One can Buy | Ice Cream | 20.5 | |||||
2009 | Mar | Every One can Buy | Ice Cream | 34.2 | |||||
2009 | Jun | Every One can Buy | Ice Cream | 43.4 | |||||
2009 | July | Every One can Buy | Ice Cream | 47.3 | |||||
2009 | Nov | Every One can Buy | Ice Cream | 19.6 | |||||
2009 | Dec | Every One can Buy | Ice Cream | 26.4 | |||||
2009 | Jan | Every One can Buy | Chips | 47.5 | |||||
2009 | Feb | Every One can Buy | Chips | 41.1 | |||||
2009 | Mar | Every One can Buy | Chips | 68.4 | |||||
2009 | Jun | Every One can Buy | Chips | 86.8 | |||||
2009 | July | Every One can Buy | Chips | 94.6 | |||||
2009 | Nov | Every One can Buy | Chips | 39.2 | |||||
2009 | Dec | Every One can Buy | Chips | 52.8 | |||||
2009 | Jan | Every One can Buy | Pizza | 52.2 | |||||
2009 | Feb | Every One can Buy | Pizza | 45.2 | |||||
2009 | Mar | Every One can Buy | Pizza | 75.3 | |||||
2009 | Jun | Every One can Buy | Pizza | 95.5 | |||||
2009 | July | Every One can Buy | Pizza | 104.1 | |||||
2009 | Nov | Every One can Buy | Pizza | 43.1 | |||||
2009 | Dec | Every One can Buy | Pizza | 58.1 | |||||
2009 | Jan | 18 or more | Beer | 15.7 | |||||
2009 | Feb | 18 or more | Beer | 13.5 | |||||
2009 | Mar | 18 or more | Beer | 22.6 | |||||
2009 | Jun | 18 or more | Beer | 28.6 | |||||
2009 | July | 18 or more | Beer | 31.2 | |||||
2009 | Nov | 18 or more | Beer | 12.9 | |||||
2009 | Dec | 18 or more | Beer | 17.4 |
Here is where I stuck. The unpivot of the first header (i.e. Every One can Buy & 18 or more) worked but cannot continue to unpivot the second layer (i.e. Ice cream, chips, pizza, beer)
Solved! Go to Solution.
Hello 🙂
You can do it easily by following below steps:
1. Get data from that Excel file and copy the query:
2. In the second query keep the top two rows only
3. Transpose the table
4. Fill down and remove empty and rename columns if needed
5. Go back to the first table and remove first row and promote header and rename needed colums and fill the year
6. Unpivot Table
7. Merge & Expand query
Hope thats answers your question.
Please don't hesitate to contact me for further support.
Best wishes 🙂
----------------------------------------------
Did I answer your question? Mark my post as a solution!
Hello 🙂
You can do it easily by following below steps:
1. Get data from that Excel file and copy the query:
2. In the second query keep the top two rows only
3. Transpose the table
4. Fill down and remove empty and rename columns if needed
5. Go back to the first table and remove first row and promote header and rename needed colums and fill the year
6. Unpivot Table
7. Merge & Expand query
Hope thats answers your question.
Please don't hesitate to contact me for further support.
Best wishes 🙂
----------------------------------------------
Did I answer your question? Mark my post as a solution!
@Motasem_Yakhola thanks a lot. I managed to tackle the issue based on your idea and note what I made it step-by-step as below.
1. Load Excel table into Power BI
2. Duplicate the table
3. Remove "promoted headers" and "change type" (you can also use "Use Header as First Row")
Result:
4. Remove the first two columns and all rows except the top 2 rows. Then, select all and click "transpose".
Result:
5. Select column 1 and "Fill" -> "Down"
Result:
6. Start to deal with the original table. Only keep the first two steps in "APPLIED STEPS" and make the table looks like below
7. Remove the first row and make the second row the headers
8. Select the first two columns and click "Unpivot Columns" -> "Unpivot Other Columns"
Result:
9. Merge this original table and the table we duplicated earlier
10. Use product name as keys to merge those two tables. Choose "Left Outer Join"
Result:
11. Select the two arrows icon on the right corner of "Sheet2(2)" column. Then, unselect column 2 (product name) and only select column1 because column 1 has the information we need.
Result:
12. Use "Fill"->"Down" to deal with all blanks in the first column. Change all column names. Done!
@Anonymous can you paste the data in table format so what it can be used instead of posting the image.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Hi Parry
I tried to create a table the same as what I got in Excel and re-posted my question just now.
Thanks!
User | Count |
---|---|
109 | |
82 | |
74 | |
53 | |
49 |
User | Count |
---|---|
133 | |
97 | |
94 | |
71 | |
69 |