Forum Discussion
Returning a boolean value via Power Query M with data from multiple columns
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
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.
Thank you for providing the example code ibarrau. I am reading up on variables in DAX right now because I am getting an error and I am not sure yet why:
Moreover I am not sure what the # represents and it also looks like this line only checks for one status ("done"). In the problem I am currently working on several statuses can be used for the boolean to return TRUE. In the example of the OP the "Jan" milestone contains "Done" and "Dropped", the boolean should still return TRUE in this case.
Thank you for your help, much appreciated.
That's because you are trying to add the column in edit query where the language is power query "m". Check this example to add DAX column:
https://docs.microsoft.com/en-us/power-bi/desktop-tutorial-create-calculated-columns
Regards.