Forum Discussion

Nicole_H's avatar
Nicole_H
Frequent Visitor
2 years ago
Solved

Dax to Power Query

I've spent way too long on this, and can't get it to work.  Can anyone covert this Dax into PowerQuery please?  I am not sure if it can be done.   The DAX code repeats [Extract_Measure] for each ID...
Need Help
  • aduguid's avatar
    aduguid
    2 years ago

    I updated my answer, hope this works for you 

     

    let
    Source = TableName,
    GroupedTable = Table.Group(Source, {"ID"}, {
        {"NewMeasure", each 
            let
                form_id = Table.FirstN(_, 1){0}[ID],
                FilteredTable = Table.SelectRows(Source, each [ID] = form_id and [Answer] = _[Extract_Measure]{0}),
                MaxValue = List.Max(FilteredTable[Extract_Measure])
            in
                MaxValue
        }
    })
in
    GroupedTable

     

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Nicole_H 

 

Please check if my approach below meets your need. 

Firstly I have data like:

Add a custom step in the Formula bar directly with below code. Replace #"Changed Type" with your previous step name. 

= Table.Group(#"Changed Type", {"ID"}, {{"All", each _, type table [ID=nullable text, Answer=nullable number, Extract_Measure=nullable number]}, {"Filter", each List.Max(Table.Column(Table.SelectRows(_, each [Answer] = [Extract_Measure]), "Extract_Measure"))}})

Expand "All" column in the previous result. Then you will have below result. "Filter" column is the new column we want. 

 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!