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 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

    • S3's avatar
      S3
      Helper III
      3 years ago

      Thanks so much Pete again. 

      This is what I'm getting now, even though no syntax error in the advanced editor:

      Expression.Error: 4 arguments were passed to a function which expects between 2 and 3.
      Details:
      Pattern=
      Arguments=[List]

      • BA_Pete's avatar
        BA_Pete
        Super User
        3 years ago

         

        I'm so sorry, that's my fault 😞 I mistyped the last code. It should be Table.TransformColumns, not Table.TransformColumnTypes.

        You should just be able to correct this in the formula bar - literally just overwrite TransformColumnTypes with TransformColumns and it should work fine.

        I'll update the code in my last post as well so as not to confuse future readers.

         

        Pete

    • S3's avatar
      S3
      Helper III
      3 years ago

      Thanks so much, it works!
      At first it still didn't work, saying that Error Type can't be changed to Function Type, then I saw that  at the end of my code was also a column called Date which I've changed with them (
      {"Date", type datetime}
      )
      and so I removed it and it worked. Thanks!