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

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
Helper III
3 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
    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

    • S3's avatar
      S3
      Helper III
      3 years ago

      Hello BA_Pete again 🙂 

      I'm trying to implement your solution in the advanced editor, it's giving me that there's a function which uses only 2 to 3 arguments..

      so actually I don't have 3 activities, I just said that to mak ethings simpler, here is what I actually have in my advanced editor, they are 5 goals and I have to track them weekly, so if a goal didn't take place, then its columns won't be present.

      I added your solution for the first step like this:

      #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),

      #"Changed Type" = Table.TransformColumns(#"Promoted Headers",{{"Visits", Int64.Type}, {"Conversions", Int64.Type}, {"Visits with Conversions", Int64.Type}, {"goal_1_nb_conversions", Int64.Type}, {"goal_1_nb_visits_converted", Int64.Type}, {"goal_2_nb_conversions", Int64.Type}, {"goal_2_nb_visits_converted", Int64.Type}, {"goal_3_nb_conversions", Int64.Type}, {"goal_3_nb_visits_converted", Int64.Type}, {"goal_4_nb_conversions", Int64.Type}, {"goal_4_nb_visits_converted", Int64.Type}, {"goal_5_nb_conversions", Int64.Type}, {"goal_5_nb_visits_converted", Int64.Type}, {"nb_visits_new_visit", Int64.Type}, {"nb_conversions_new_visit", Int64.Type}, {"nb_visits_converted_new_visit", Int64.Type}, {"goal_1_nb_conversions_new_visit", Int64.Type}, {"goal_1_nb_visits_converted_new_visit", Int64.Type}, {"goal_2_nb_conversions_new_visit", Int64.Type}, {"goal_2_nb_visits_converted_new_visit", Int64.Type}, {"goal_3_nb_conversions_new_visit", Int64.Type}, {"goal_3_nb_visits_converted_new_visit", Int64.Type}, {"goal_4_nb_conversions_new_visit", Int64.Type}, {"goal_4_nb_visits_converted_new_visit", Int64.Type}, {"goal_5_nb_conversions_new_visit", Int64.Type}, {"goal_5_nb_visits_converted_new_visit", Int64.Type}, {"nb_visits_returning_visit", Int64.Type}, {"nb_conversions_returning_visit", Int64.Type}, {"nb_visits_converted_returning_visit", Int64.Type}, {"goal_1_nb_conversions_returning_visit", Int64.Type}, {"goal_1_nb_visits_converted_returning_visit", Int64.Type}, {"goal_2_nb_conversions_returning_visit", Int64.Type}, {"goal_2_nb_visits_converted_returning_visit", Int64.Type}, {"goal_3_nb_conversions_returning_visit", Int64.Type}, {"goal_3_nb_visits_converted_returning_visit", Int64.Type}, {"goal_3_revenue_returning_visit", Int64.Type}, {"goal_4_nb_conversions_returning_visit", Int64.Type}, {"goal_4_nb_visits_converted_returning_visit", Int64.Type}, {"goal_5_nb_conversions_returning_visit", Int64.Type}, {"goal_5_nb_visits_converted_returning_visit", Int64.Type}, {"Date", type datetime}},
      null,
      MissingField.Ignore
      ),


      but I'm getting Expression.Error: We cannot convert Type to Function type.
      Details:
      Value=[Type]
      Type=[Type]

      • BA_Pete's avatar
        BA_Pete
        Super User
        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