Forum Discussion

tobiasmcbride's avatar
tobiasmcbride
Helper III
6 years ago
Solved

Importing table segmented by regions

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 ChinaWorld
Q1Q2...Q1Q2...Q1Q2... 

 

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?

Need Help
  • HotChilli's avatar
    HotChilli
    6 years ago

    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.

4 Replies

  • HotChilli's avatar
    HotChilli
    Community Champion
    6 years ago

    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.

  • Icey's avatar
    Icey
    Community Support
    6 years ago

    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.

  • Icey's avatar
    Icey
    Community Support
    6 years ago

    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