Forum Discussion

S3's avatar
S3
Helper III
3 years ago
Solved

A More Dynamic Refresh?

Hello everyone,  I have a question regarding the refresh I make every 3 days to the same table. The issue is that the columns are based on the activity which happened and it makes problems with th...
Need Help
  • BA_Pete's avatar
    BA_Pete
    3 years ago

    Hi S3 ,

     

    *EDIT* Corrected mistyped function in code window:

    Table.TransformColumnTypes > Table.TransformColumns.

     

    You're getting this error with my code as you're trying to apply data types (Int64.Type) to each column instead of applying a function (Number.From, Text.From etc.).

    Your new changed types step should actually look more like this:

     

    #"Changed Type" =
Table.TransformColumns(
    #"Promoted Headers",
    {
        {"Visits", Number.From},
        {"Conversions", Number.From},
        {"Visits with Conversions", Number.From},
        {"goal_1_nb_conversions", Number.From},
        {"goal_1_nb_visits_converted", Number.From},
        ...
        ...
    },
    null,
    MissingField.Ignore
)

     

     

    Pete

  • BA_Pete's avatar
    BA_Pete
    3 years ago

    Hi S3 ,

     

    Apologies for the delay. A hero has answered the call:

    Try this, credit to MarkLaf :

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("FcqxDQAxCATBXi62BJwNNrUg+m/j+WQ1wVaBFKNQqVgw7uMxmN6X6FVQE3WxjH/Id8PPYLpp6P4A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Activity 1" = _t, #"Activity 2" = _t, #"Activity 3" = _t]),
    transformTypes =
    Table.TransformColumns(
        Source,
        {
            {"Activity 1", each Date.From(_, "en-GB"), type date},
            {"Activity 2", each Number.From(_, "en-GB"), type number},
            {"Activity 3", each Number.From(_, "en-GB"), type number}
        },
        null,
        MissingField.Ignore
    )
in
    transformTypes

     

    Pete

BA_Pete's avatar
BA_Pete
Super User
3 years ago

 

No problem. You just need to add the Table.TransformColumns code I gave you as a custom step wherever you want to change types in your code, and adjust the 'Date.FromText' etc. functions to suit the types changes you want to make.

Copy this and paste the whole lot over the default code in Advanced Editor to see it in action:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("FcqxDQAxCATBXi62BJwNNrUg+m/j+WQ1wVaBFKNQqVgw7uMxmN6X6FVQE3WxjH/Id8PPYLpp6P4A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Activity 1" = _t, #"Activity 2" = _t, #"Activity 3" = _t]),
    transformTypes =
    Table.TransformColumns(
        Source,
        {
            {"Activity 1", Date.FromText},
            {"Activity 2", Number.From},
            {"Activity 3", Number.From}
        },
        null,
        MissingField.Ignore
    )
in
    transformTypes

 

 

You'll see the types change between the Source step and the transformTypes step.

If you select the Source step, then delete any of the columns, you'll then see that PQ still maks the type changes on the remaining columns without error.

 

Pete

S3's avatar
S3
Helper III
3 years ago

Hello BA_Pete, 

Thanks so much! I have a deadline till tomorrow, so I won't be able to test this before. In the next two days I'll try it and will get back to you, I'm sure it works though, thanks so much