A More Dynamic Refresh?

[S3]

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 the refresh. If Activity 1, Activity 2, Activity 3 happen, then perfect, but if Activity 1 and Activity 2 take place, then the Steps I did to change Data Type for all the columns will include the Activity 3 column, which doesn't exist now because it didn't take place in the past 3 days. 

Is there a way to make this more flexible please? Or is the only solution is to go throughout the steps each time I do a refresh and change accordingly manually?

Thank you. 

[BA_Pete · Accepted solution]

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]

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]

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]

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]

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 · Accepted solution]

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]

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!

[S3]

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]

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]

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]

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]

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]

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]

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!

[BA_Pete · Accepted solution]

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]

I would like to ask your opinion about another long standing issue I still don't know why it's taking place, it still persists even when I change the Month Name to Month Name Year (so that I really specify which month in which year). It would be great to hear from you what you think please:

https://community.powerbi.com/t5/DAX-Commands-and-Tips/semi-additive-measure-only-working-with-month...

[S3]

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 🙂 

[S3]

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