Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
S3
Helper III
Helper III

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

2 ACCEPTED SOLUTIONS

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

17 REPLIES 17
BA_Pete
Super User
Super User

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




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

245CF554-0E84-474B-B615-E614BBAF8040.jpeg

 

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




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]

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




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!

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]

 

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




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 😕 

 

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




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
)

 

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




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!

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




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

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 🙂 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors