Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
StuBee
Helper I
Helper I

Headers Changing Position

Hi Everyone,

 

I recieve files each week with a double header Row

First row as country (but a merged cell) Second row is a list of units etc

sp   UK   Total   
CountUnitsSalesROSCountUnitsSalesROSCountUnitsSalesROS
      (1) 00(1)0
 1    2 0030

 

Until recently I could use the second row and rename, however new countries have been added.

 

Would anyone be able to help with either

1)how to filter all columns after 'Name' (which is column 2) and 'Total' (which is columnd 20 and later 24)

 

2) Or how to dynamically rename the headers based on the column name to the left (if the current column -1 = total, "Total Count")

 

Any help or suggestions would be appreciated.

 

Thanks

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

Hi  @StuBee ,

 

For this to be dinamic one of the options is:

  • Create a new table with the same source of the previous one and keep only the two top rows:

Table 1

MFelix_0-1612350773564.png

Table 2

MFelix_1-1612350798442.png

  • On the second table
  • Add an index
  • Select all columns and unpivot

 

MFelix_2-1612350834348.png

  • On the Column Value replace all blanks by null
  • Insert a custom column that retrieves the Column number from the Attribute column

MFelix_3-1612350907832.png

  • Sort rows by (attention of order):
    • Text after delimiter (column created in the previous step)
    • Index

MFelix_4-1612350983325.png

  • Add a custom column with the following code:
if [Index] = 0 then [Value] else null

 

  • On the column you just created do fill down:

MFelix_5-1612351047707.png

  • Add a new custom column with the following code:
if [Value] = [Custom] then null else [Custom] &" - " & [Value]

MFelix_6-1612351121886.png

 

 

  • Remove all columns except attribute and the FinalHeader
  • Filter all null values from the final header
  • Select all columns
  • Pivot and select dont'aggregate

MFelix_7-1612351195093.png

  • Go to the first table
  • Append
  • Now append the second table on the first one
  • Be aware that you need to redo your step on the formula bar and change the order of the tables:

MFelix_8-1612351283511.png

  • Promote first row has headers and remove top 2 rows
  • Disable load on table2
  • This will now be dinamic if you add more columns.

Check PBIX file attach.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

2 REPLIES 2
StuBee
Helper I
Helper I

Thanks very much! This is better than what I was thinking, which was to just move the changed files into a different folder and join them

 

Thanks again

MFelix
Super User
Super User

Hi  @StuBee ,

 

For this to be dinamic one of the options is:

  • Create a new table with the same source of the previous one and keep only the two top rows:

Table 1

MFelix_0-1612350773564.png

Table 2

MFelix_1-1612350798442.png

  • On the second table
  • Add an index
  • Select all columns and unpivot

 

MFelix_2-1612350834348.png

  • On the Column Value replace all blanks by null
  • Insert a custom column that retrieves the Column number from the Attribute column

MFelix_3-1612350907832.png

  • Sort rows by (attention of order):
    • Text after delimiter (column created in the previous step)
    • Index

MFelix_4-1612350983325.png

  • Add a custom column with the following code:
if [Index] = 0 then [Value] else null

 

  • On the column you just created do fill down:

MFelix_5-1612351047707.png

  • Add a new custom column with the following code:
if [Value] = [Custom] then null else [Custom] &" - " & [Value]

MFelix_6-1612351121886.png

 

 

  • Remove all columns except attribute and the FinalHeader
  • Filter all null values from the final header
  • Select all columns
  • Pivot and select dont'aggregate

MFelix_7-1612351195093.png

  • Go to the first table
  • Append
  • Now append the second table on the first one
  • Be aware that you need to redo your step on the formula bar and change the order of the tables:

MFelix_8-1612351283511.png

  • Promote first row has headers and remove top 2 rows
  • Disable load on table2
  • This will now be dinamic if you add more columns.

Check PBIX file attach.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors