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
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:
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.
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.
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 <3
-L
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 RemovedColumns2Hello.
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
