Forum Discussion
Solved
Dynamically Rename to ID column if ID column is missing
Hello Everyone, From sharepoint, I am getting a series of lookup columns which have the following format: ApplicationId Application ServerId Server EnvironmentId Environment ...
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
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
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.
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 Custom1That seems to do the trick. Thank you very much for your help!