Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
love
Helper I
Helper I

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 costum column should be TRUE. If any other status for a given milestone is present, it should return FALSE:

 

 

How can I put this into a Power Query M formula? I am stuck on how to query multiple rows (milestones) to be a variable for my custom column. Is what I am asking for possible in this data structure?

 

Thank you!

-anon

1 ACCEPTED SOLUTION

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

View solution in original post

12 REPLIES 12
Ashish_Mathur
Super User
Super User

Hi @love,

 

This calculated column formula works

 

=CALCULATE(COUNTROWS(Table1),FILTER(Table1,Table1[Milestone]=EARLIER(Table1[Milestone])&&(Table1[Status]="Done"||Table1[Status]="Dropped")))=CALCULATE(COUNTROWS(Table1),FILTER(Table1,Table1[Milestone]=EARLIER(Table1[Milestone])))

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thanks @MarkS and @Ashish_Mathur for the follow up. Your answers helped me understanding the errors I made and are a great help for future projects.

 

 

MarkS
Resolver IV
Resolver IV

Hi @love

I think that this is a way to do what you requested in M.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMU9JRcsnPS1WK1YFzi/ILClJTkEXQFMC4bqlJmFy//BIFuJBvYhGyCigXyQKICBY9eIViAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Milestone = _t, Status = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Milestone", type text}, {"Status", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Milestone"}, {{"Aggregate", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each if(List.Count(List.RemoveItems(Table.Column([Aggregate],"Status"),{"Dropped","Done"})))>0 then "False" else "True"),
    #"Expanded Aggregate" = Table.ExpandTableColumn(#"Added Custom", "Aggregate", {"Status"}, {"Aggregate.Status"})
in
    #"Expanded Aggregate"

Change the source to your source table.

Thank you all for your input and the provided suggestions! I tried all of them to the best of my abilities, here my results:

 

@ibarrau

Okay, I am pretty sure I get where/what M/DAX now is. Apologies for the confusion.

I tried your suggestion in a column (thank you for the link) but got a syntax error:

 

The syntax for 'RETURN' is incorrect (DAX(QUERY1[MilestoneName1]RETURN IF(COUNTROWS(FILTER(QUERY1;QUERY1[MilestoneName1] = ActualMile)) = COUNTROWS(FILTER(QUERY1;QUERY1[MilestoneName1] = ActualMile && QUERY1[MilestoneName1] = "Done")); TRUE();FALSE()))).

 

VAR ActualMile = QUERY1[MilestoneName1]
RETURN IF(COUNTROWS(FILTER(QUERY1;QUERY1[MilestoneName1] = ActualMile)) = COUNTROWS(FILTER(QUERY1;QUERY1[MilestoneName1] = ActualMile && QUERY1[MilestoneName1]="Done"));TRUE();FALSE())

 

 

I replaced "Table" with my actual source in PBI which is a query called "QUERY1" and "Milestone" with the actual column name which is "MilestoneName1". I also tried with "Column =" at the beginning which highlighted "ActualMile" in green and assume therefore recognized it as VAR.

 

 

@Interkoubess

Thank you for both your suggestions. I tried both and managed to get both to work. Sadly they don't meet one of my requirements which is that the boolean output has to reference the status of ALL rows for one milestone. With the provided code my output will be TRUE/FALSE based on the status of a single row, not based on the statuses of all rows of a given milestone.

 

@MarkS

I replaced source table and updated the column names and status names to match the actual ones from my example. I get the following expression error:

 

Expression.Error: We cannot convert a value of type Table to type Function.

Details:

  Value=Table

  Type=Type

 

let
    Source = QUERY1(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMU9JRcsnPS1WK1YFzi/ILClJTkEXQFMC4bqlJmFy//BIFuJBvYhGyCigXyQKICBY9eIViAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [MilestoneName = _t, CaseStatus = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"MilestoneName", type text}, {"CaseStatus", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"MilestoneName"}, {{"Aggregate", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each if(List.Count(List.RemoveItems(Table.Column([Aggregate],"CaseStatus"),{"dropped","passed"})))>0 then "False" else "True"),
    #"Expanded Aggregate" = Table.ExpandTableColumn(#"Added Custom", "Aggregate", {"CaseStatus"}, {"Aggregate.CaseStatus"})
in
    #"Expanded Aggregate"

I really appricated all your detailed help ❤️

-L

Hi @love,

I see that you now have a solution.  

With the suggestion that I provided the Source step should have been changed to  

 

Source=Query1,

 

the (Json.Document ....) should be deleted

MarcelBeug
Community Champion
Community Champion

The example for Feb looks incorrect to me: it should get both falses.

 

An alternative approach below. The comments should clarify what is happening,

 

let
    Source = Table1,

    // First determine which Milestones have statuses other than "Done" or "Dropped"
    Filtered = Table.SelectRows(Source, each [Status] <> "Done" and [Status] <> "Dropped"),
    RemovedColumns1 = Table.SelectColumns(Filtered,{"Milestone"}),
    RemovedDuplicates = Table.Distinct(RemovedColumns1),


    // Now merge the original table with the result from the previous step:
    Merged = Table.NestedJoin(Source,{"Milestone"},RemovedDuplicates,{"Milestone"},"Falses",JoinKind.LeftOuter),

    // If the nested table in column "Falses" is emtpy then the custom column must be true, otherwise false
    // In other words, function Table.IsEmpty returns the desired result
    AddedCustom = Table.AddColumn(Merged, "Custom", each Table.IsEmpty([Falses]), type logical),

    // Remove the join column:
    RemovedColumns2 = Table.RemoveColumns(AddedCustom,{"Falses"})
in
    RemovedColumns2
Specializing in Power Query Formula Language (M)

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

Interkoubess
Solution Sage
Solution Sage

Hi @love,

 

if you still want to have the result in M then you can create a custom column with this formula:

 

if [Status]="Done" or [Status]="Dropped" then "TRUE" else "FALSE"

otherwise there is the formula to paste in the advanced editor (please change your source):

 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Milestone", type text}, {"Status", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Status]="Done" or [Status]="Dropped" then "TRUE" else "FALSE")
in
    #"Added Custom"

Let us know if you have any observations...

 

Ninter

ibarrau
Super User
Super User

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.

 

 

 


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

Happy to help!

LaDataWeb Blog

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.


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

Happy to help!

LaDataWeb Blog

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 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.