Change the code below from DAX to Power Query

[jij19]

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!

[wdx223_Daniel]

NewStep=Table.Group(PreviousStepName,"Reference Number",{"Avg Activation Time", each List.Average(List.RemoveNulls([#"Activation (wd)"]))})

[v-eqin-msft]

Hi @jij19 ,

Could you please share some screenshots /your applied steps / sample data to help us clarify your scenario?

Best Regards,
Eyelyn Qin

[v-eqin-msft]

Hi @jij19 ,

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.

[jij19]

Thanks a lot, this works but when I expand all the other columns than the group by function doesn't work anymore and I get rows with the same Reference number.. How can I solve this?

[jij19]

And furthermore it also counts blanks rows for the average which is not the right solution for me 😞

[johnt75]

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.