Dynamically Rename to ID column if ID column is missing

[MAVIE]

Hello Everyone, 

From sharepoint, I am getting a series of lookup columns which have the following format:

ApplicationId	Application	ServerId	Server	EnvironmentId	Environment
1	Record	1	Record	1	Record

However, these lookup columns can be changed to a custom column type within sharepoint, such that I would recieve the data in this format: 

Application	Server	Environment
1	1	1

I would like the rename the second table to have the naming conversion "_Id", however I do not know ahead of time which column types have been changed within sharepoint, and thus I could potentially recieve a table which looks like the following: 

Application	ServerId	Server	Environment
1	1	Record	1

Therefore, I need to dynamically check if the ID column exists, and if it does not exist then rename the corresponding column to be the ID column. In the table above for example, it would be renaming {{"Application", "ApplicationId"}, {"Environment", "Environment"}}. 

There are over 100 columns in the entire table, and this is only relevant for some of these columns. I have a list of the exact columns that might be affected, but do not know wheter or not some have been changed.

Any help would be greatly appreciated.

[Vijay_A_Verma]

Inset this step in your code

= Table.TransformColumnNames(Source,(x)=>if List.Contains(Table.ColumnNames(Source),if Text.EndsWith(x,"Id") then x else x&"Id") then x else x&"Id")

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAeOg1OT8ohQwJzYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Application = _t, ServerId = _t, Server = _t, Environment = _t]),
    Custom1 = Table.TransformColumnNames(Source,(x)=>if List.Contains(Table.ColumnNames(Source),if Text.EndsWith(x,"Id") then x else x&"Id") then x else x&"Id")
in
    Custom1

[MAVIE]

Perhaps I should have clarified. There are over 100 columns in the table and this has to be applied to only some of these. I have a list of all of the columns where this is relevant for.

[Vijay_A_Verma · Accepted solution]

Use below code. ListOfColumns is that list which needs to be populated. You can also keep this list in a separate list query and replace ListOfColumns by your list.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAeOg1OT8ohQoRyk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Application = _t, ServerId = _t, Server = _t, Environment = _t, abc = _t]),
    ListOfColumns = {"Application", "abc", "Server"},
    Custom1 = Table.TransformColumnNames(Source,(x)=>if List.Contains(ListOfColumns,x) then if List.Contains(Table.ColumnNames(Source),x&"Id") then x else x&"Id" else x)
in
    Custom1

[MAVIE]

That seems to do the trick. Thank you very much for your help!