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

MarcelBeug's avatar
MarcelBeug
Community Champion
8 years ago

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
love's avatar
love
Helper I
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