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.
I have a collection of tables containing datetime fields which need to be converted from text to datetime in Power Query, as I've done below for the CreatedAt field (and need to do for the ModifiedAt field)
I also have a few identically named fields in every single table that I'd like to exclude in the data load (fields recording data warehouse update times)
Currently, I need to click manually into each table (or Advanced Editor script) and insert the identical transformations.
Is there any way to automate the application of identical transformations to multiple tables, e.g.
Solved! Go to Solution.
Hi @Anonymous
Download example PBIX file showing the below scenario
If the transformations you want to do on each table are identical then you can create a function to do these transforms and call it to do its stuff on each table.
First create a query that carries out all the transformations. Something like this
Source = XXXXXXXXXXX,
#"Removed Columns" = Table.RemoveColumns(Source,{"DeleteMe1", "DeleteMe2"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"ModifiedAt", type datetime}, {"CreatedAt", type datetime}})
in
#"Changed Type"
Then modify this query to look like this
(tab as table) => let
Source = tab,
#"Removed Columns" = Table.RemoveColumns(Source,{"DeleteMe1", "DeleteMe2"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"ModifiedAt", type datetime}, {"CreatedAt", type datetime}})
in
#"Changed Type"
This changes the query into a function that can be called from other queries. It takes 1 parameter, a table called tab.
Save the query and rename it to fxTableTransforms
Now in your other queries you can call this function e.g.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fYwxDoUgEAWvQqiFvLcLuO5VDIV6/zuIhfkWX5tpZjLrGgWCRCTMgeYqTmQzKdri9C23bYCMfXpcLHBU6rVkVtbW5Ln5a/f9SuT3wZKkBM6OxaVlLaBB78+rPY4B1dj7CQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ModifiedAt = _t, CreatedAt = _t, DeleteMe1 = _t, DeleteMe2 = _t]),
Tab = fxTableTransforms(Source)
in
Tab
After you load your data in the Source step,insert a step that calls the function andpasses the Source table into it
Tab = fxTableTransforms(Source)
What you get returned ino Tab is your transformed table.
NOTE: The column names need to be the same in all tables you want to modify.
Regards
Phil
Proud to be a Super User!
Hi @Anonymous
Download example PBIX file showing the below scenario
If the transformations you want to do on each table are identical then you can create a function to do these transforms and call it to do its stuff on each table.
First create a query that carries out all the transformations. Something like this
Source = XXXXXXXXXXX,
#"Removed Columns" = Table.RemoveColumns(Source,{"DeleteMe1", "DeleteMe2"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"ModifiedAt", type datetime}, {"CreatedAt", type datetime}})
in
#"Changed Type"
Then modify this query to look like this
(tab as table) => let
Source = tab,
#"Removed Columns" = Table.RemoveColumns(Source,{"DeleteMe1", "DeleteMe2"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"ModifiedAt", type datetime}, {"CreatedAt", type datetime}})
in
#"Changed Type"
This changes the query into a function that can be called from other queries. It takes 1 parameter, a table called tab.
Save the query and rename it to fxTableTransforms
Now in your other queries you can call this function e.g.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fYwxDoUgEAWvQqiFvLcLuO5VDIV6/zuIhfkWX5tpZjLrGgWCRCTMgeYqTmQzKdri9C23bYCMfXpcLHBU6rVkVtbW5Ln5a/f9SuT3wZKkBM6OxaVlLaBB78+rPY4B1dj7CQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ModifiedAt = _t, CreatedAt = _t, DeleteMe1 = _t, DeleteMe2 = _t]),
Tab = fxTableTransforms(Source)
in
Tab
After you load your data in the Source step,insert a step that calls the function andpasses the Source table into it
Tab = fxTableTransforms(Source)
What you get returned ino Tab is your transformed table.
NOTE: The column names need to be the same in all tables you want to modify.
Regards
Phil
Proud to be a Super User!
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.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |