Importing table segmented by regions

[tobiasmcbride]

Hi,

I have a table I am importing which sources revenue data from 3 regions: ROW, China & World (enables us to breakdown by the regions).

Currently table is formatted like so:

ROW

China

World

Q1

Q2

...

Q1

Q2

...

Q1

Q2...

Hence we have merged cells for ROW, China & world and underneath we have each of the financial quarters. The data then follows from that.

When importing this into PowerBI, what would be the best way to format the table so that it is read friendly and enables me to still see which region I am looking at when segmenting the revenue data?

[Icey]

Hi @tobiasmcbride ,

Is this problem solved?

If it is solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If not, please let me know.

Best Regards
Icey

[Icey]

Hi @tobiasmcbride ,

I agree with @HotChilli . And after that, you can use "Fill" -> "Down" to fill you "Region" column. Can this method solve your problem?

Best Regards,
Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

This a complex format to be given to power BI.

Try to keep it as raw as possible like database table data.

You can refer to unpivot options here

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

[HotChilli · Accepted solution]

In Power Query, if you import the table (without promoting headers), you should be able to Transpose it.

In the first column, you'll have to replace empty string with null.  Then do a fill down to get ROW, China etc populated.