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)

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 only3. Transpose the table4. 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 year6. Unpivot Table7. 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!

Forum Discussion

Anonymous

Not applicable

6 years ago

Solved

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 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)

Need Help

Motasem_Yakhola
6 years ago
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!

4 Replies

Motasem_Yakhola
Helper I
6 years ago
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!
- Anonymous
  Not applicable
  6 years ago
  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!
parry2k
Super User
6 years ago
Anonymous can you paste the data in table format so what it can be used instead of posting the image.
- Anonymous
  Not applicable
  6 years ago
  Hi Parry
  
  I tried to create a table the same as what I got in Excel and re-posted my question just now.
  
  Thanks!