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

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

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

  • S3's avatar
    S3
    Helper III
    3 years ago

    Hello Pete, 

    sorry to bother you again, but the TransformColumns and Missigngfields.ignore when used with locale are returning me the "can't change type to Function Type" error. I tried researching ont he net and I tried everything, it didn't work out 😕 

  • BA_Pete's avatar
    BA_Pete
    Super User
    3 years ago

     

    Can you send a screenshot of the bit of code where you're trying to use a locale transformation please? It's possible this is a limitation of the technique, but I'll have a look for you.

     

    Pete

  • S3's avatar
    S3
    Helper III
    3 years ago

    Thank you! If it's one column, it works, so I should probably do it on each column seperately? 

    #"Changed Type with Locale" = Table.TransformColumns(#"Extracted Text Before Delimiter",
    {
    {"goal_1_conversion_rate", type number, "en-GB"},
    {"goal_3_conversion_rate_returning_visit", type number, "en-GB"},
    {"goal_2_conversion_rate_returning_visit", type number, "en-GB"},
    {"goal_1_conversion_rate_returning_visit", type number, "en-GB"},
    {"conversion_rate_new_visit", type number, "en-GB"},
    {"goal_5_conversion_rate_new_visit", type number, "en-GB"},
    {"goal_4_conversion_rate_new_visit", type number, "en-GB"},
    {"goal_3_conversion_rate_new_visit", type number, "en-GB"},
    {"goal_2_conversion_rate_new_visit", type number, "en-GB"},
    {"goal_1_conversion_rate_new_visit", type number, "en-GB"},
    {"Conversion Rate", type number, "en-GB"},
    {"goal_5_conversion_rate", type number, "en-GB"},
    {"goal_2_conversion_rate", type number, "en-GB"},
    {"goal_3_conversion_rate", type number, "en-GB"},
    {"goal_4_conversion_rate", type number, "en-GB"}
    },
    null,
    MissingField.Ignore
    )

  • BA_Pete's avatar
    BA_Pete
    Super User
    3 years ago

     

    You're mixing up function capabilities here.

    The logical combination of TransformColumns and TransformColumnTypes would be something like this, I think:

    Table.TransformColumns(
        Table.TransformColumnTypes(
            Source,
            {
                {"Activity 1", type date},
                {"Activity 2", type text},
                {"Activity 3", type text}
            },
            "en-GB"
        ),
        {
            {"Activity 1", Date.From},
            {"Activity 2", Number.From},
            {"Activity 3", Number.From}
        },
        null,
        MissingField.Ignore
    )

     

    However, the TransformColumnTypes part would need to evaluate before TransformColumns, so your MissingField.Ignore argument wouldn't get a chance to skip the missing column error.

    I was thinking maybe to use Table.TransformColumnTypes instead of Number.From, but you're not working across a table at that point so no good either.

    This has me a bit stumped to be honest, but I have some very powerful friends 😉 I'll send up the Bat Signal and get some proper heroes to take a look!

     

    Pete

  • S3's avatar
    S3
    Helper III
    3 years ago

    Thanks so much Pete for your help! The explanation you've alreadys offered is very helpful, anything extra would also be good to know, but you've already helped a lot!

  • S3's avatar
    S3
    Helper III
    3 years ago

    Thanks so much BA_Pete  for trying to help with the solution, remembering this and getting back at me!
    Thank you @MarkLaf for the solution of this complicated question of mine!

    Your help is much appreciated 🙂