Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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
Custom1
That seems to do the trick. Thank you very much for your help!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.