Search code examples
daxpowerquerym

Convert DAX if statement to Power Query (or M)?

I need to create a calculated column in order to filter a Tabular model table with the following structure:

Table1

| ID  | Attr A    | Attr B | Value |
|-----|-----------|--------|-------|
| 123 | text here | blah   | 130   |
| 123 | blah      | blah   | 70    |
| 456 | blah      | blah   | 90    |
| 456 | blah      | blah   | 110   |

And I want the following new column to be created:

| ID  | Attr A    | Attr B | Value | MaxValue |
|-----|-----------|--------|-------|----------|
| 123 | text here | blah   | 130   | TRUE     |
| 123 | blah      | blah   | 70    | FALSE    |
| 456 | blah      | blah   | 90    | FALSE    |
| 456 | blah      | blah   | 110   | TRUE     |

I would like to create a calculated column using Power Query equivalent to the following DAX statement which returns TRUE if the Values column is the largest for a given ID, FALSE otherwise. 

= IF(CALCULATE(MAX('Table1'[Value]),ALLEXCEPT('Table1','Table1'[ID])) = 'Table1'[Value], TRUE(), FALSE())

P.S. I used the default M language editor to generate an if shell statement so this is similar to what I'm looking for:

= Table.AddColumn(#"Changed Type", "MaxValue", each if [#"[Value]"] = 'some logic here' then true else false)
Solution

  • If your source table is set up like this and called Table1:

    enter image description here

    Then this M code should do what you're asking:

    let
    Source = Table1,
    #"Grouped Rows" = Table.Group(Source, {"ID"}, {{"ValueMax", each List.Max([Value]), type number}, {"AllData", each _, type table [ID=text, Attr A=text, Attr B=text, Value=number]}}),
    #"Expanded AllData" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"Attr A", "Attr B", "Value"}, {"Attr A", "Attr B", "Value"}),
    #"Added Custom" = Table.AddColumn(#"Expanded AllData", "MaxValue", each [ValueMax]=[Value]),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"ID", "Attr A", "Attr B", "Value", "MaxValue"})
in
    #"Removed Other Columns"

    It should give you this result:

    enter image description here