Forum Discussion

619SK's avatar
619SK
Helper II
2 years ago
Solved

If blank then concat - Power Query

power

I want to concat "S.no" , "Name" & Valid in power query with the below format.

Concat only if cell is not blank like below. 

 

S.noNameValidAppend Result 
1AYesS.No:1 | Name: A | Valid: Yes
2B S.No:2 | Name: B
3 NoS.No:3 | Valid: No
4C S.No:4 | Name: C
Need Help
  • OwenAuger's avatar
    OwenAuger
    2 years ago

    Hi 619SK 

    You can use Text.Combine for this since it automatically excludes null values.

    For example:

     

    let
  Source = #table(
    type table [S.no = Int64.Type, Name = text, Valid = text],
    {{1, "A", "Yes"}, {2, "B", null}, {3, null, "No"}, {4, "C", null}}
  ),
  #"Added Append Result" = Table.AddColumn(
    Source,
    "Append Result",
    each Text.Combine({"S.No:" & Text.From([S.no]), "Name: " & [Name], "Valid: " & [Valid]}, " | "),
    type text
  )
in
  #"Added Append Result"

     

    For this method to work, blanks would need to be null values rather than empty strings.

     

    Does something like this work for you?

1 Reply

  • OwenAuger's avatar
    OwenAuger
    Super User
    2 years ago

    Hi 619SK 

    You can use Text.Combine for this since it automatically excludes null values.

    For example:

     

    let
  Source = #table(
    type table [S.no = Int64.Type, Name = text, Valid = text],
    {{1, "A", "Yes"}, {2, "B", null}, {3, null, "No"}, {4, "C", null}}
  ),
  #"Added Append Result" = Table.AddColumn(
    Source,
    "Append Result",
    each Text.Combine({"S.No:" & Text.From([S.no]), "Name: " & [Name], "Valid: " & [Valid]}, " | "),
    type text
  )
in
  #"Added Append Result"

     

    For this method to work, blanks would need to be null values rather than empty strings.

     

    Does something like this work for you?