Forum Discussion
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 |
| 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.
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
4 Replies
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 Custom1Perhaps 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 Custom1
