How to make header of report as column in report while loading

[Anonymous]

Hi Team,

Have input file like bellow.

i need output as bellow in power BI

How to achive above output in Power BI

[Anonymous]

The easiest solution is to import the data for 4 region in separate queries, add a column named "Region" to all 4 and then append the data back.

Steps -

1. Import Query 1 - Keep first 4 columns (Africa) & remove other columns, add new column named "Region" with value "Africa"

   Import Query 2 - Keep columns 6-9 (US) & remove others, add new column named "Region" with value "US"

  Import Query 3 - Keep columns 11-14 (Asia) & remove others, add new column named "Region" with value "Asia"

  Import Query 4 - Keep columns 16-19 (Europe) & remove others, add new column named "Region" with value "Europe"

2. Now Append the 4 queries created in step 1.

P.s. please make sure same data type is selected for same columns across 4 queries

Hope this helps.

[Anonymous]

Thanks for quick response.

Have one more query, is their any option to split columns auotomatically and apped query isted of manual column split and append

[Anonymous]

Try this -

1. Keep region names above the first column. i.e. "US" should be in the cell right above "Order Date"

2. Import the file into Power query

3. Remove the blank columns between regions

4. Go to 'Transform' tab and click "Transpose"

5. Promote 'First row as headers'

6. Rename first column as 'Region'

7. select first column, go to "Transform" tab, click "Fill" option and select Fill "Down". This will populate the regions

8. Select first 3 columns (other than those which have date as column name), then right click on selected column and select "Unpivot other columns"

This should arrive you at the desired format. Then you can rename all columns as required.

[amitchandak]

@Anonymous ,

You have a few options. But it better to do outside power bi.

Create 4 copies of this excel. in 1st one keep the first 4 columns remove the other. In 2nd one keep the next 4 columns and remove the others. Select the 4 right-click sn use remove other .

Append these 4 tables.

Another option is in this blog

https://kohera.be/blog/power-bi/how-to-unpivot-twice/