Excel Format to Load in Power BI

[Poojara_D12]

Hi members,

I have a issue to load data from excel to Power, actually the format of excel is in such a way that it is not being fesiable in Power BI, so I need help of all the genious present here.

Format of excel:

Format of Power BI

It has around 4 different header defining different things, how will that be possible in Power BI to have the exact same kinda headers or something like that. I've tried using transpose,pivot.  

I want all the headers that are there in different rows as a single header, so that I can create a report out of the same. 

Please help me with this issue.

Thanks in advance!!

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
YouTube: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS

[shafiz_p · Accepted solution]

Hi @Poojara_D12  It is hard to give solution without representative data for such scenario. But in normal case, if header is in multiple rows, transpose and merge related header column and transpose back would solve the problem. Giving you a simple example:

Header spans multiple row in this image:

First transposed:

Then merged:

Transpose back:

Now promoted as header. and you are done.


Hope this helps!
If this solved your problem, please accept it as a solution and a kudos!!

Best Regards,
Shahariar Hafiz

[FreemanZ]

hi @Poojara_D12 ,

this typically involves some trial with demote header, merge columns, transpose, unpivot, conditional columns, fill down, etc...

try to post representative sample data

or check some previous posts like:

https://community.fabric.microsoft.com/t5/Power-Query/Transform-multi-row-header-from-Excel-Table-in...