How to change column names by row values from another column?

[esquiveljc]

Hello,
I have the column names in rows. Let me explain: Column1 must be replaced by the only value in Agg1 therefore "Country", Column2 must be replaced by the only value in Agg2 therefore "Cities" and so on.

Column1	Column2	Column3	Agg1	Agg2	Agg3
United States	Miami	483395	Country	Cities	Population
United States	New York	8804190	Country	Cities	Population
United States	Los Angeles	3971883	Country	Cities	Population
United States	Chicago	2720546	Country	Cities	Population
United States	Seattle	737015	Country	Cities	Population
United States	San Francisco	864816	Country	Cities	Population
United States	Boston	696959	Country	Cities	Population
United States	Houston	2296224	Country	Cities	Population
United States	Philadelphia	1603797	Country	Cities	Population
United States	Dallas	1300092	Country	Cities	Population
United States	Phoenix	1608139	Country	Cities	Population
Canada	Vancouver	600000	Country	Cities	Population
Canada	Montreal	1600000	Country	Cities	Population
Canada	Calgary	1019942	Country	Cities	Population
Canada	Quebec	528595	Country	Cities	Population
Canada	Toronto	2600000	Country	Cities	Population
Canada	Winnipeg	632063	Country	Cities	Population
Canada	Ottawa	812129	Country	Cities	Population
Canada	Edmonton	712391	Country	Cities	Population
Canada	Halifax	359111	Country	Cities	Population
Canada	Hamilton	519949	Country	Cities	Population
Canada	Regina	176183	Country	Cities	Population
France	Marseille	794811	Country	Cities	Population
France	Paris	2138551	Country	Cities	Population
France	Bordeaux	231844	Country	Cities	Population
France	Lyon	472317	Country	Cities	Population
France	Nice	338620	Country	Cities	Population
France	Strasbourg	274845	Country	Cities	Population
France	Toulouse	433055	Country	Cities	Population
France	Lille	228328	Country	Cities	Population
France	Nantes	277269	Country	Cities	Population
France	Dijon	149782	Country	Cities	Population
France	Amiens	143086	Country	Cities	Population

The goal is to put the correct names of the axes and the legend without hardcoding it.

I saw that you can't do this via DAX: 

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Change-column-header-names-dynamically-in-a-t...

Do you know if there is another way to do this please?

Thanks

[esquiveljc · Accepted solution]

The only way I've found is to eliminate the two axis names in Power BI and replace them with a visual card.

[Surya9]

@esquiveljc you can rename the column name 1,2& 3 as Country , Cities and Populations respectively in power query mode (Transform Data ) 

[esquiveljc]

@Surya9Thanks for your quick response, but as written in my post I can't hardcode because the Country, Cities and Population column names may change in the datasource.