Replace column name only if its wrong

[masplin]

Hi

Sure this is simply for you clever people! I havebene givne a folder containing 400 hundred folders each containing several exel files.  I hoped thefiles al lhad an idnetical strucutre, but they don't. I havefound some of the older files use the column name "Formal Notice Date" in the 6th column, whereas newer files call the same field "At Risk Date".  So is there a way to test if the 6th column has this heading and replace it if it does otherwise do nothing?

I had some luck with this

 Table.RenameColumns(#"Promoted Headers",{{Table.ColumnNames(#"Promoted Headers"){5}, "At Risk Date"}})

but fell over when on a file column 5 and 6 were reversed so now column 5 was already called "At Risk Date"

I tried to make up sme syntax, but clearly my M is not up to the task. I couldn't work out the else do nothing.

= Table.RenameColumns(#"Promoted Headers", if Record.Field(_,Table.ColumnNames(#"Promoted Headers"){5}="Formal Notice Date") then {{Table.ColumnNames(#"Promoted Headers"){5}, "At Risk Date"}} else {{Table.ColumnNames(#"Promoted Headers"){5}, Record.Field(_,Table.ColumnNames(#"Promoted Headers"){5}}}))

Since its clear the columns could be in any order ideally it would test if any column was caleld "Formal Notice Date" and replace it with "At Risk Date".

Appreicate any help as impossible for me to locate thefiles that have slightly differnet structure.

Mike

[AlB · Accepted solution]

Hi @masplin 

I don't understand what you are trying to do with Record.Field. Try this:

= if Table.ColumnNames(#"Promoted Headers"){5} = "Formal Notice Date" then Table.RenameColumns(#"Promoted Headers",{{"Formal Notice Date", "At Risk Date"}}) else #"Promoted Headers"

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[AlB · Accepted solution]

@masplin 

= if List.Contains( Table.ColumnNames(#"Promoted Headers"), "Formal Notice Date") then Table.RenameColumns(#"Promoted Headers",{{"Formal Notice Date", "At Risk Date"}}) else #"Promoted Headers"

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[masplin]

brilliant thnaks so much

[AlB · Accepted solution]

Hi @masplin 

I don't understand what you are trying to do with Record.Field. Try this:

= if Table.ColumnNames(#"Promoted Headers"){5} = "Formal Notice Date" then Table.RenameColumns(#"Promoted Headers",{{"Formal Notice Date", "At Risk Date"}}) else #"Promoted Headers"

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[masplin]

My M is like speaking Frenech from a phrase book!!!   That's great thnaks

Is there a way to make this if any column has that name as found sometimes it was column 4 called "Formal notice date"? I realsie I coudl just repeat the clause multiple times to test each column.