cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
charliedata
Responsive Resident
Responsive Resident

Automating Power Query development

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)

 

charliedata_0-1615229983632.png

 

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.

  • Remove all these fields from all tables
  • Convert all these fields to datetime

 

 

 

1 ACCEPTED SOLUTION
PhilipTreacy
Super User III
Super User III

Hi @charliedata 

 

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



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

1 REPLY 1
PhilipTreacy
Super User III
Super User III

Hi @charliedata 

 

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



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors