Headers Changing Position

[StuBee]

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
Count	Units	Sales	ROS	Count	Units	Sales	ROS	Count	Units	Sales	ROS
						(1)		0	0	(1)	0
	1					2		0	0	3	0

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

[StuBee]

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 · Accepted solution]

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

Table 2

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

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

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

• Add a custom column with the following code:

if [Index] = 0 then [Value] else null

• On the column you just created do fill down:

• Add a new custom column with the following code:

if [Value] = [Custom] then null else [Custom] &" - " & [Value]

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

• 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:

• 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.