How to rename columns in Power Query that change the name on each data updating - T0029

It is not uncommon having to deal with reports with column headers related to dates that change each time we get new data. Columns with no constant names in Power Query are an issue because Power Query expects to deal always with the same column names. Unless we teach it how to deal with the unexpected.
__________________

Get this ready-to-use Excel solution for 2 and 3 columns of Dependent Dropdown Lists:
---

Chapters:

0:00 – Introducing the problem
1:34 – Celia introduces herself and her Youtube channel
3:08 – EXAMPLE 1: variable column names like “Sales of Wk of 12/27”
3:31 – Importing data from external Excel File with Power Query and cleasing data
6:00 – Creating Base step
6:40 - Introducing the Function Table.ColumnNames
7:04 - Introducing the Function Table.RenameColumns
9:56 – Applying the Function Table.ColumnNames
10:58 – Selecting specific items from the list of columns names
13:15 – Creating list of new names for columns
14:44 - Introducing the Function List.Zip
15:17 - Applying the Function List.Zip
16:48 – Applying the Function Table.RenameColumns
18:03 – Finishing Query
18:54 – Testing Query
20:36 – EXAMPLE 2: variable column names like “2021-12-27”, Importing data from external Excel File with Power Query and cleasing data
22:03 – Creating Base step
22:23 – Applying the Function Table.ColumnNames
22:45 – Selecting date items from the list of columns names
24:36 – Creating list of new names for columns
25:09 - Applying the Function List.Zip
27:02 – Applying the Function Table.RenameColumns
29:01 - Finishing Query

M Code Functions :

-------------------



Celia Alves - Microsoft MVP & Certified Excel Expert, Solutions Developer
Twitter: @celia_excel
Instagram: @solveandexcel


#excel #msexcel #exceltraining #msexceltraining #powerquery #powerquerytraining #dataanalysis #snapreports #solveandexcel #automation #reportautomation #VBA

[GrainneDuggan_Excel]

Excellent solution to a problem that pops up in so many scenarios.

[wayneedmondson]

Hi Celia. Great way to start the new year! An awesome and useful lesson. A variation I employed, instead of the List.Select() construct you used to isolate the last 3 column names for the OldNames step, I used List.LastN as follows, = List.LastN(Table.ColumnNames(Base), 3). Assuming you are always after the last 3 column names, then this construct eliminates the need to trap for the old column names with Text.Contains() or the method you used in example 2 by changing the data type and filtering out the errors. Using List.LastN() works for both examples, regardless of the starting column names. List.Zip() is a great find. Thanks for demonstrating and providing the inspiration to learn something new. Looking forward to more in 2022 :)) Thumbs up!!

[iankr]

Brilliant! Great techniques there - many thanks, Celia.

[ziggle]

Another great post! This is one of the most common problems I encounter. Nice, flexible solution.

[GopiNath-fply]

You are always giving very nice alternative solutions. Thank you.

[krishanphophalia]

Awesome, Celia 👏👏👏 You have explained so well. 😊

[er]

It took me quite a while to find this answer. Thank you. Great content

[mattschoular]

That was a great example. Thanks Celia

[jazzista]

Celia, this video was very informative: I like the way you dissected the problems into little pieces and troubleshoot the last part of the solution. will practice those 3 functions in M code. Thaks for sharing. Great examples

[kebincui]

Thank you very much Celia. Very informative presentation 👍👍🌹🌹

[ExcelOffTheGrid]

Nice solution - it's always exciting to find useful functions in the Power Query documentation :-)

[goodnewskasparyaodzramedo]

Thanks. You make power query look easy and interesting

[FRANKWHITE]

Very informative. Thanks for sharing ❤

[tlee]

Thank you so much! You’re a life saver …🙏

[HachiAdachi]

Thank you for introducing this approach to renaming the columns and to List.Zip function!
I don't know what the refresh performance difference would be, but my approach involves unpivoting and pivoting back:

(after the 'Base' step)
UnpivotedOtherColumns =
Table.UnpivotOtherColumns(
Base,
{"Brand Item", "On Hand", "Allocations", "Available to Promise", "On Order", "PR On Hand"},
"Attribute", "Value"
),
AddedIndex = Table.AddIndexColumn(UnpivotedOtherColumns, "Index", 0, 1, Int64.Type),
CalculatedModulo = Table.TransformColumns(AddedIndex, {{"Index", each Number.Mod(_, 3), type number}}),
ReplacedValues =
Table.ReplaceValue(
CalculatedModulo, each [Index], each
if [Index] = 0 then "Last Week Sale"
else if [Index] = 1 then "Two Weeks Ago"
else "Three Weeks Ago",
Replacer.ReplaceValue, {"Index"}
),
RemovedColumns = Table.RemoveColumns(ReplacedValues, {"Attribute"}),
PivotedColumn = Table.Pivot(RemovedColumns, List.Distinct(RemovedColumns[Index]), "Index", "Value", List.Sum)

Most of these steps can be performed with UI except for "ReplacedValues" step. Again, no idea how this performs compared to your solution, but just wanted to share my 2 cents...

[JayZ]

Great video very helpful. I have a dumb ? What if you wanted to retain the column names as they are and have them change with the source? Not sure that is even possible. Just wondering as I have a vendor file i pull in every week. Changing the name helped so I can automate but wondering if I'll lose track of the time period

[VipinThakur]

Thanks for sharing. It is amazing. You explained it very well.

I have another issue hope you can help.

Issue:
What would be the solution if Column name and posting of the column keep changing.

Ex:
Column A: Name, Column B: E-mail.

Next time I get the data in this following format

Column A: E-mail, Column B: Full Name

This is the big issue I experienced. Do you have any solution for this

[alanbotten]

I have to change two months in the same Sheet Jan and Feb, How do I create Oldname and Newname for both with the List.Select function. If i do both separately the end result is not correct?

[lionels]

Another option is to relate the rename table (with old and new names) to the data table (with old names) in the data model if the new names are used in pivot tables