If blank then concat - Power Query

[619SK]

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.no	Name	Valid	Append Result
1	A	Yes	S.No:1 | Name: A | Valid: Yes
2	B		S.No:2 | Name: B
3		No	S.No:3 | Valid: No
4	C		S.No:4 | Name: C

[OwenAuger · Accepted solution]

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?