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

NewMeasure
var form_id = 'Table'[ID]

RETURN
MAXX(
FILTER(
ALL('Table'),
'Table'[ID] = form_id
&& 'Table'[Answer] = 'Table'[Extract_Measure]
),
'Table'[Extract_Measure]
)

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

     

8 Replies

  • Nicole_H's avatar
    Nicole_H
    Frequent Visitor
    2 years ago

    Thanks all, I did eventually get aduguid's solution to work

  • aduguid's avatar
    aduguid
    Memorable Member
    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

     

    • Nicole_H's avatar
      Nicole_H
      Frequent Visitor
      2 years ago

      Thanks for your speedy reply!  Although I don't get a syntax error the new column is returning Error, not sure what I have done wrong (or what I'm doing at all to be honest!)

  • ronrsnfld's avatar
    ronrsnfld
    Super User
    2 years ago

    Please provide some usable data (as text, not a screenshot) along with what you expect for results from that data, and the logic required to go from Source data to Results.

    • Nicole_H's avatar
      Nicole_H
      Frequent Visitor
      2 years ago

      Sorry ronrsnfld, I'm using really sensitive data, which is why I gave a vague example, but if the other solutions don't work I'll try and attach something.  Thanks

      • ronrsnfld's avatar
        ronrsnfld
        Super User
        2 years ago

        If you do, use made up data that reproduces your problem. Don't use real data.

  • 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!