Forum Discussion
A More Dynamic Refresh?
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
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
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
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 😕
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!