Forum Discussion

OleMcDonald's avatar
OleMcDonald
Helper I
5 years ago
Solved

Power Query Create Column Syntax

I'm trying to get to..

I am trying to create a Treu/False column with this logic

If [Is Target] is true then True
or else if
If [AccountTypeGroup] =Client then True
or else if
[AnnualRevenue] >= 250000000 and [Has Products] = "True" and [Marketing Country] = "True" then True
else False

I dont think my syntax is right

 

if [AccountTypeGroup] = "Client" then true else if [Is Target] = "True" then true else if [AnnualRevenue] >= 250000000 and [Has Products] = "True" and [Marketing Country] = "True" then true else false
Need Help
  • PhilipTreacy's avatar
    PhilipTreacy
    5 years ago

    Hi OleMcDonald 

    Download an example PBIX file with the below code in it.

     

    If you could supply your actual data it would help becaue it's not clear if your true/false values are Boolean true/false or if you are using text strings.

    If you are using Boolean True/False then your data would look like this

    and you would use this query to create a Custom Column

     

    if [AccountTypeGroup] = "Client" or [Is Target] = true 

then true 

else if [AnnualRevenue] >= 250000000 and [Has Products] = true and [Marketing Country] = true

then true 

else false

     

     

    If you are using text columns for True/False you're data will look something like this (notice the icons in the column headers are different for True/False compared to the Boolean columns above)

    Using text adds another complication in that the text string True is not the same as the text string true, but you could use a query like this

     

    if [AccountTypeGroup] = "Client" or [Is Target] = "True" 

then true 

else if [AnnualRevenue] >= 250000000 and [Has Products] = "True" and [Marketing Country] = "True" 

then true 

else false

     

     

    Regards

    Phil

    If I answered your question please mark my post as the solution.
    If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

3 Replies

  • OleMcDonald's avatar
    OleMcDonald
    Helper I
    5 years ago

    All of your replies were extermely helpful. Thank you all for taking the time to help.

    I didnt know "True" and true were not the same. So, I went back and edited the columns I created to true / false rather than "True" / "False".

    Thanks again

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    5 years ago

    Hi,

    Remove the "" from around the true.  Also, Mashup ("M") - the language of the Query Editor is case sensitive.  So ensure that case of true matches with the case of true in the 3 columns.

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    5 years ago

    Hi OleMcDonald 

    Download an example PBIX file with the below code in it.

     

    If you could supply your actual data it would help becaue it's not clear if your true/false values are Boolean true/false or if you are using text strings.

    If you are using Boolean True/False then your data would look like this

    and you would use this query to create a Custom Column

     

    if [AccountTypeGroup] = "Client" or [Is Target] = true 

then true 

else if [AnnualRevenue] >= 250000000 and [Has Products] = true and [Marketing Country] = true

then true 

else false

     

     

    If you are using text columns for True/False you're data will look something like this (notice the icons in the column headers are different for True/False compared to the Boolean columns above)

    Using text adds another complication in that the text string True is not the same as the text string true, but you could use a query like this

     

    if [AccountTypeGroup] = "Client" or [Is Target] = "True" 

then true 

else if [AnnualRevenue] >= 250000000 and [Has Products] = "True" and [Marketing Country] = "True" 

then true 

else false

     

     

    Regards

    Phil

    If I answered your question please mark my post as the solution.
    If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.