Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

Data Transformation for Excel with two (or more) headers

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 CreamChipsPizzaBeer    
2019Jan $             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    

 

          
2009JanEvery One can BuyIce Cream         23.7     
2009FebEvery One can BuyIce Cream         20.5     
2009MarEvery One can BuyIce Cream         34.2     
2009JunEvery One can BuyIce Cream         43.4     
2009JulyEvery One can BuyIce Cream         47.3     
2009NovEvery One can BuyIce Cream         19.6     
2009DecEvery One can BuyIce Cream         26.4     
2009JanEvery One can BuyChips         47.5     
2009FebEvery One can BuyChips         41.1     
2009MarEvery One can BuyChips         68.4     
2009JunEvery One can BuyChips         86.8     
2009JulyEvery One can BuyChips         94.6     
2009NovEvery One can BuyChips         39.2     
2009DecEvery One can BuyChips         52.8     
2009JanEvery One can BuyPizza         52.2     
2009FebEvery One can BuyPizza         45.2     
2009MarEvery One can BuyPizza         75.3     
2009JunEvery One can BuyPizza         95.5     
2009JulyEvery One can BuyPizza      104.1     
2009NovEvery One can BuyPizza         43.1     
2009DecEvery One can BuyPizza         58.1     
2009Jan18 or moreBeer         15.7     
2009Feb18 or moreBeer         13.5     
2009Mar18 or moreBeer         22.6     
2009Jun18 or moreBeer         28.6     
2009July18 or moreBeer         31.2     
2009Nov18 or moreBeer         12.9     
2009Dec18 or moreBeer         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)

transformation_3.JPG

 

1 ACCEPTED SOLUTION

Hello 🙂

You can do it easily by following below steps:

1. Get data from that Excel file and copy the query:

1.PNG

 

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

2.PNG

 

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

3.PNG

 

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!

View solution in original post

4 REPLIES 4

Hello 🙂

You can do it easily by following below steps:

1. Get data from that Excel file and copy the query:

1.PNG

 

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

2.PNG

 

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

3.PNG

 

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!

Anonymous
Not applicable

@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

 

1.JPG

 

2. Duplicate the table

 

2.JPG

 

3. Remove "promoted headers" and "change type" (you can also use "Use Header as First Row")

 

3_1.jpg

 

Result:

 

4.JPG

 

4. Remove the first two columns and all rows except the top 2 rows. Then, select all and click "transpose". 

 

5.JPG

 

Result:

 

7.1.jpg

 

 

5. Select column 1 and "Fill" -> "Down"

 

Result:

 

8.1.JPG 

 

6. Start to deal with the original table. Only keep the first two steps in "APPLIED STEPS" and make the table looks like below

 

9.JPG

 

7. Remove the first row and make the second row the headers

 

10.JPG

 

8. Select the first two columns and click "Unpivot Columns" -> "Unpivot Other Columns"

 

12.1.JPG

 

Result:

 

12.JPG

 

9. Merge this original table and the table we duplicated earlier

 

13.1.JPG

 

10. Use product name as keys to merge those two tables. Choose "Left Outer Join" 

 

14.JPG

 

Result:

 

15.JPG

 

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.

 

16.1..JPG

 

Result:

 

18.JPG 

 

12. Use "Fill"->"Down" to deal with all blanks in the first column. Change all column names. Done! 

 

19.JPG

 

 

parry2k
Super User
Super User

@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.

Anonymous
Not applicable

Hi Parry

 

I tried to create a table the same as what I got in Excel and re-posted my question just now.

 

Thanks!

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors