Automating Power Query development

[Anonymous]

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.

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

[PhilipTreacy · Accepted 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