Need help with M code equivalent of a DAX measure

[nh27]

Hi all,

I'm stuck with trying to write some M code as I would like the data to be stored in the table as some of my visuals at the moment do not allow drill through as I am referencing the DAX Measure on them.

The DAX measure itself essentially is filtering the table based on Column A = "Not Ready". Of those filtered records, also check if ANY Column B, C, D are BLANK. If they are, then I need to create a new Column E stating they are "Not Evaluated". If ANY of NOT BLANK then it can remain as column A "Not Ready".

The DAX Measure I have:

Not Evaluated =

VAR _NotReady =

    FILTER (

        'Table1',

        'Table1'[Column A] = "Not Ready Yet"

    )

VAR _NotEvaluated_ =

    FILTER (

        _NotReady,

        'Table1'[Column B] = BLANK() ||

        'Table1'[Column C] = BLANK() ||

        'Table1'[Column D] = BLANK()

    )

VAR _Count =

    COUNTROWS ( _NotEvaluated_ )

RETURN

    _Count

Any help would be appreciated here, many thanks in advance.

[johnbasha33]

@nh27 

To achieve the desired outcome using M code in Power Query, you can follow these steps:

1. Load your table into Power Query.
2. Add a custom column with the following M code:

```m
= if [Column A] = "Not Ready Yet" and ([Column B] = null or [Column C] = null or [Column D] = null) then "Not Evaluated" else [Column A]
```

Replace `[Column A]`, `[Column B]`, `[Column C]`, and `[Column D]` with the actual column names in your table.

This code checks if Column A equals "Not Ready Yet" and if any of Columns B, C, or D are blank. If both conditions are true, it assigns "Not Evaluated" to the new column; otherwise, it retains the value from Column A.

3. Click "Close & Load" to load the modified table back into Excel.

This will create a new column (Column E) in your table where the values are "Not Evaluated" if Column A is "Not Ready Yet" and any of Columns B, C, or D are blank. Otherwise, it will contain the value from Column A.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

[nh27]

Hi, thank you for this suggestion. Unfortunately it's not quite getting me the expected results. The above is returning "Not Evaluated" where ALL three conditions are null, not if ANY of them are null/blank.

Is there a different operator I can use here as or does not seem to work in the same way as || ?

[nh27 · Accepted solution]

Just thought I'd follow up on this as I managed to get it to work and in case anyone else faces similar issues.
I found alot of my data did not tag as null, some cells were just blank and others were null. I had to do a find/replace on blanks and label them null for it to do the trick.