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

Interkoubess's avatar
Interkoubess
Solution Sage
8 years ago

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