Load and transformation from excel with merged cells to table into power bi

[twister8889]

Hello, guys

I would like to transform my excel data, into a power query, to present my data as a table without merged cells. How can do I do my data stay by column?

I think that result with power query, is like table 3 in the image below:

[amitchandak]

@twister8889 , You can unpivot on a level easily in power bi

https://radacad.com/pivot-and-unpivot-with-power-bi

@ImkeF , can you help on this

[ImkeF]

Hi @twister8889 

the link provided above will not solve your problem.

If you want to unpivot multiple header rows, you can use the function I've created here: https://www.thebiccountant.com/2017/06/19/unpivot-by-number-of-columns-and-rows-in-powerbi-and-power...

For your example here, the second parameter has to be a 1 (one column to keep) and the third parameter a 3 ( 3 header rows to unpivot )

[twister8889]

First of all, thank you for your answer

I have the follow results, 

1) using the unpivot 

    - To the country column, ok, but I still have a problem with other columns.

2) Using the unpivot by number of columns

    - I lost countries data (C1, C2)

[ImkeF · Accepted solution]

Hi @twister8889 ,

try to demote the header first before applying my function.

So that the rows to unpivot are actually the first 3 rows in the table and not one of them a table header.

[looloo]

Hi there, is there a video on how to do this? I am new to PBI and am struggling to do the first step. Thank you