Forum Discussion
Change the code below from DAX to Power Query
Hello Community
I found this code for dax, where the measure calculates the average days for the same id in multiple rows.
It works with DAX but I would like to do the same in Power Query, so that I can do the change in the data transformation.
Avg Activation Time =
var Oppy_id = 'Calculation'[Reference number]
Return
AVERAGEX( FILTER(ALL('Calculation'), 'Calculation'[Reference number] = Oppy_id), 'Calculation'[Activation (wd)])
Thanks a lot!
6 Replies
Group by the [Reference number column] and create an average of [Activation (wd)]. Then restore the previous step to bring the full table back and add a new column grabbing the value from the step you inserted.
Hi Anonymous ,
Please firstly use Group by:
Then expand necessary columns:
Below is the whole M syntax:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0UIrVgTAt4SwLOMsczHICsszgLFM4ywTMcgayjOEsIzDLBWS0UmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Reference number" = _t, #"Activation (wd)" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Reference number", type text}, {"Activation (wd)", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Reference number"}, {{"All", each _, type table [Reference number=nullable text, #"Activation (wd)"=nullable number]}, {"Average", each List.Average([#"Activation (wd)"]), type nullable number}}), #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Activation (wd)"}, {"Activation (wd)"}) in #"Expanded All"Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.NewStep=Table.Group(PreviousStepName,"Reference Number",{"Avg Activation Time", each List.Average(List.RemoveNulls([#"Activation (wd)"]))})
