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 formulaAre there any ways to replace this "null" by nothing in my formula ?

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

Composing text with null values replaced by nothing

wdx223_Daniel
Community Champion
5 years ago
Text.Combine function can ignore the null value in its first parameter.
Fowmy
Super User
5 years ago
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
Anonymous
Not applicable
5 years ago
Hey there. You can do this:
NewStep = Table.AddColumn(PriorStepName, "NewColumnName", each if [Column2] = null then [Column1] else [Column1]&[Column2], type text)

---Nate
Anonymous
Not applicable
2 years ago
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