Composing text with null values replaced by nothing

[Hnd12000]

I'm trying to compose a text (to define the location of a file) that is made of the combination of Drilled down values (concatenated with "&")

The problem I face is that there are instances when some of this Drill down can be empty.

In Excel concatenating cells that are empty will not add anything but in PowerQuery empty is null and this ruin my formula

Are there any ways to replace this "null" by nothing in my formula ?

[sandermatthijs]

Maybe the solution by @CNENFRNL is more elegant, easier to use in case you have more columns (like I had)?

Table.AddColumn(#"Replaced Value", "Custom", each ([A]??"") & ([B]??"") & ([C]??""))

Solved: Custom column to combine two Colum returns null if... - Microsoft Fabric Community

[Anonymous]

Hey there. You can do this:

NewStep = Table.AddColumn(PriorStepName, "NewColumnName", each if [Column2] = null then [Column1] else [Column1]&[Column2], type text)

---Nate

[Fowmy]

@Hnd12000 


You may try to replace null with blank using:

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[wdx223_Daniel · Accepted solution]

Text.Combine function can ignore the null value in its first parameter.