Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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 th...
Need Help
Anonymous's avatar
Anonymous
Not applicable
4 years ago

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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?

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

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