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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.