Convert DAX to Power M Query

[TeresaBusby]

First time trying to convert DAX to Power M Query and having a hard time getting started/finding exactly what I need in the help documents. Can someone help me convert this?  And are there any resources/cheat sheets to help do this?  For example "Calculate" in DAX is "X" in Power M, etc.

This is my DAX which gives me the count of Type's per Id.

CALCULATE (

    COUNTROWS ( 'Table' ) ,

    ALLEXCEPT (

        'Table' ,

        'Table'[ID],

        'Table'[Type]

    )

)

[Anonymous · Accepted solution]

Hi @TeresaBusby 

You can use group by in power query to count rows, you can refer to the folloing link.

Group rows of data (Power Query) - Microsoft Support

You can put the following code to advanced editor in power query to as a sample

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUpUitVBZyXBWclglhFc1gguawSRjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Type = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Type", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID", "Type"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mlsx4]

Hi @TeresaBusby 

I'm not sure why you need to traslate a DAX to Power M Query. Anyway, in the official doc, you can find every formula for M language (https://learn.microsoft.com/en-us/powerquery-m/power-query-m-function-reference).

In the case of your formula, you may need a grouping function (https://radacad.com/grouping-in-power-query-getting-the-last-item-in-each-group)