Forum Discussion

love's avatar
love
Helper I
8 years ago
Solved

Returning a boolean value via Power Query M with data from multiple columns

 Hello.   I would like a custom column to return a boolean value depending on the data in other columns.   Example: If all "statuses" from one "milestone" are either "done" or "Dropped" the costu...
  • love's avatar
    love
    8 years ago

    Hello.

     

    Thank you for your reply MarcelBeug.

     

    I ended up finding an alternative solution. I was able to query for additional fields in my base query, one of which was an indicator for completion which makes my initial problem irrelevant and seems like the overall more elegant solution.

     

    I will try to check out your proposal at a later stage out of curiousity.

     

    Thanks again everybody.

    -l

ibarrau's avatar
ibarrau
Super User
8 years ago

Hey there, is there a reason you want to do it on power query m and not in DAX? Let me see if i understand. The result of what you look for is like this:

Milestone Status CustomColumn
Jan            Done  FALSE

Jan            NOT    FALSE

Feb           Done  TRUE

Feb           Done  TRUE

 

You want to check in each row if the hole Jan rows are done and then put TRUE? This would be so much properly and easy to make on dax:

 

VAR ActualMile = Table[Milestone]
RETURN IF(COUNTROWS(FILTER(Table;Table[Milestone] = ActualMile)) = COUNTROWS(FILTER(Table;Table[Milestone] = ActualMile && Table[Status]="Done"));TRUE();FALSE())

 

Regards, let me know if it works.

 

 

 

love's avatar
love
Helper I
8 years ago

Hello ibarrau.

 

You are right with using DAX. I am new to the M/DAX world and misunderstood the split between M being used for the actual query and DAX for data analysis.

 

Thank you for your quick reply. I'll check out your suggestion and report back with my findings :)