Forum Discussion
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.
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
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 transformTypesPete
17 Replies
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
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..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 transformTypesYou'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
