Power Query concatenate columns with conditional settings?

[macfuller]

Our ERP system has vendor information in multiple formats, thanks to various tweaks over the years. In this case we are dealing with Business Enterprise info (minority, veteran, women-owned, ...) A vendor can have multiple categories but over the years our programmers have used either binary flags or text for the various categories.

I would like to get a single field with the various conditions, but I'm getting errors when i try to combine IF statements.

if [#"Women-Owned"] = "Yes" then "Women-Owned " else "" 
& if [Veteran Flag] = "Y" then "Veteran " else ""
& if [Disabled Flag] = "Y" then "Disabled " else ""
& if [EEOC] <> "" then [EEOC] else ""

Is there a way to do this without having to create a new column for each flag and concatenate those?

 

[GraH]

Maybe add some brackets, like (if .... then .... else) & (if .... then .... else), ...

or perhaps

[CODE]
= Text.Combine(
         List.Combine (
        {
            { if [#"Women-Owned"] = "Yes" then "Women-Owned " else "" }
           ,{ if [Veteran Flag] = "Y" then "Veteran " else "" }
           ,{ if [Disabled Flag] = "Y" then "Disabled " else "" }
           ,{ if [EEOC] <> "" then [EEOC] else "" }
        }
    )
    , " " )

 

[macfuller]

Yes thanks, that works.

 #"Added Custom" = Table.AddColumn(AddxBECol, "Test", each
        Text.Combine (
            List.Combine ( 
            {
                {if [#"Women-Owned"] = "Yes" then "Women-Owned" else ""},
                {if [Veteran Flag] = "Y" then "Veteran" else ""},
                {if [Disabled Flag] = "Y" then "Disabled" else ""}
            }
                ), " "
            )
        )

I'm not familiar with Text.Combine. It appears to be putting the delimiter space before the secondary arguments even if there's no primary. In other words, if "Veteran" is the only value in the list then it's returning " Veteran" with a space instead of "Veteran". Am I making a mistake in the syntax? I would have thought the function would be smart enough not to put a delimiter before the first value returned so I must be doing it wrong.

 

[GraH]

Hi, try this.
Replace else "" by else null.
Use list.RemoveNulls before List.Combine. leave the text.combine as is.

 

[macfuller]

Just the null replacing "" seems to work. Thanks for the tip!

 

[GraH]

That's actually good to know. As I did not expect that, since the list does contain nulls and I somehow expected it would create a text "Women-Owner,,".
The Microsoft documentation does not explicitly tell Text.Combine dismisses null values. Implicitly it says a list of text values and null is not a text.

Glad I could help. And you learned me something too. Thanks!

 

[Rickmaurinus]

Your code is well on the way. It just needs some slight adjustment with the parenthesis. You can put each statement between parenthesis, and then use the Combine Operator to concatenate them.

 (if [#"Women-Owned"] = "Yes" then "Women-Owned " else "" )
& ( if [Veteran Flag] = "Y" then "Veteran " else "" )
& ( if [Disabled Flag] = "Y" then "Disabled " else "" )
& ( if [EEOC] <> "" then [EEOC] else "" )

An alternative would be to use the Text.Combine function:

Text.Combine(
  {
    (if [#"Women-Owned"] = "Yes" then "Women-Owned " else ""),
    (if [Veteran Flag] = "Y"     then "Veteran "     else ""),
    (if [Disabled Flag] = "Y"    then "Disabled "    else ""),
    (if [EEOC] <> ""             then [EEOC]         else "")
  }
)

Lastly, if you want to concatenate your values based on a condition, I think you'll enjoy reading this article.

Concatenate Values in Power Query - BI Gorilla

Learn how to Concatenate Values with Power Query. Find out how to handle delimiters, data types, and conditions like a pro.

gorilla.bi

It delves into concatenating values. But also focuses on different data types, using delimiters and concatenating null values.

Cheers,
Rick