power query to determine which only select the highest stage in each job number

[CWhite82]

Hi,  In Power Query, how do I write dax to say which cvc_stage is to be used, i'm wanting the use the highest stage on each job number but struggling to put this into dax

In Excel i did an if(A1=A2, "Don't use" , "Use") - this worked as the report always has job # follwed by the stage # but obviously i can't write this into dax,

Any help would be much apprciated 

[lbendlin · Accepted solution]

In Power Query you write M code, not DAX.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Anonymous · Accepted solution]

Hi @CWhite82 ,

@lbendlin Thanks for your reply!

And @CWhite82 , just as @lbendlin said, Power Query use M code, and Power BI Desktop use DAX. I will give you both solutions of M code and DAX.

Here is my sample data:

If you want to use DAX, then in Power BI Desktop, use this DAX to create a calculated column:

Flag = 
VAR _maxstage = 
CALCULATE(
    MAX('DAX'[cvc_stage]),
    ALLEXCEPT('DAX', 'DAX'[job_num])
)
RETURN
IF(
    'DAX'[cvc_stage] = _maxstage,
    "Use",
    "Don't use"
)

And the final output is as below:

If you want to use Power Query, you can put this M code into a new Blank Query > Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjYwMjQ3UdJBMMyB2FApVgeXnBEeOWOonLGJpRlYDsqwQDITm5wRHjl8ZppA5UxMLSzBclCGJZJ92OSA9sUCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [job_num = _t, css_series = _t, cvv_valno = _t, cvc_stage = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"job_num", type text}, {"css_series", type text}, {"cvv_valno", Int64.Type}, {"cvc_stage", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"job_num"}, {{"Max", each List.Max([cvc_stage]), type nullable number}}),
    #"Merged Queries" = Table.NestedJoin(#"Grouped Rows", {"job_num"}, #"Changed Type", {"job_num"}, "Table", JoinKind.FullOuter),
    #"Expanded Table" = Table.ExpandTableColumn(#"Merged Queries", "Table", {"css_series", "cvv_valno", "cvc_stage"}, {"css_series", "cvv_valno", "cvc_stage"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Table", "Custom", each if [Max] = [cvc_stage] then "Use" else "Don't use")
in
    #"Added Custom"

And the final output is as below:

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

[CWhite82]

Hi @Anonymous , @lbendlin ,

Thanks so much for this, I went with the PwrBI Dax option as felt more compftable with DAX and worked perfectly, mind blown

Massively apprciately the time in helping out

Thanks again
Chris

[lbendlin · Accepted solution]

In Power Query you write M code, not DAX.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...