Forum Discussion

S3's avatar
S3
Icon for Helper III rankHelper III
4 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
    4 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
Icon for Super User rankSuper User
4 years ago

Hi S3 ,

 

You can use Table.TransformColumns to change your data types, and use the MissingField.Ignore parameter, something like this:

Table.TransformColumns(
    previousStepName,
    {
        {"Activity 1", Date.FromText},
        {"Activity 2", Number.From},
        {"Activity 3", Text.From}
    },
    null,
    MissingField.Ignore
)

 

More info here:

https://docs.microsoft.com/en-us/powerquery-m/table-transformcolumns 

 

Pete

  • S3's avatar
    S3
    Icon for Helper III rankHelper III
    4 years ago

    Hello BA_Pete, 

    Thanks for your reply and the explanation, I'm very excited that there's a solution for it. However, even after reading the article I'm not so sure how to implement it properly..

    • BA_Pete's avatar
      BA_Pete
      Icon for Super User rankSuper User
      4 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
        Icon for Helper III rankHelper III
        4 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