Case insensitive column rename with mapping table

[Goodkat]

Dear friends of Power Query,

With the help of the forum, and two insightful websites (a big, big thank you to Imke Feldman thebiccountant.com and Rick de Groot gorilla.bi) I was able to further improve my data flows. Especially having unified naming of column names, disregardless from which chaotic source they are from, makes all following steps much easier. Hence, over the last days I came up with a long list of renamings and realized that doing this case insensitive would be beneficial as it reduces the number of master data entries I need to look after.

So with the help of Rick de Groot's Tutorial I managed a crisp case insensitive renaming of items when they are in a list.

Yesterday I reviewed another information source in which it does make most sense to replace directly in the column names. I thought to use the replacement in a list and implement it into the Table.TransformColumnNames.

And here I fail. I have tried since two hours various possible constellations which I have used on header renaming in the past; but without success. I also tried to combine it into a Table.RenameColumns, but never got it running.

Does anyone from the community have an idea how to use the replacement logic that works properly case insensitive also in a direct renaming of column names?

https://c.gmx.net/@324888734501700174/z3Eoy1QdSoyMiT3Bx-LraA

I am eager to learn, how I would need to adapt the code!

Thank you a lot in advance!

Best regards, Andreas

[Goodkat]

Dear Vijay,

it works perfect. Brilliant! I have never seen the 'try' / 'otherwise' commands before!

Good learning! And the headers are now as they are supposed to.

Thank you so much!

Best regards, Andreas

[Vijay_A_Verma · Accepted solution]

Use this as your next step

= Table.TransformColumnNames(Quelle, (x)=>try mdTable[NewText]{List.PositionOf(mdTable[OldText], x, 0,Comparer.OrdinalIgnoreCase)} otherwise x)