Power Query combining NULL values

[vsolanon]

Hi Experts,

I am creating my fist PowerBi which is linked to SharePoint lists. I have a column within the SP List named "Author" which is a Person column. Due to this, when I changed the properties to "Fist Name and Last Name", I got two different columns.

Since I want to display the two columns in one field, I created a a new column named "Author" (the one that is highlighted in the above image) using the below formula.

ColumnCreated= [Author.FistName]& " "& [Author."LastName]

My main issue is that when I combine both columns, I got a null value since one of the columns combine is null.  Could you please guide me through to know how this can be fix. In case a column is null, I would like to only display the next that has a value.

Kindly note I have tried using "isblank" and "if" statement wihout success. Also, the order of the column cannot be change since I need to display first the name and then the last name.

Thank you in advance!

[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

[Matthew_Theis]

If the nulls are replaced with blank, and all columns are Text data type, you can create a custom column and "concantenate" the fields together with an "&".  This is the most elegant solution to your problem.

[jvanegmond]

Although the if-then-else solution works, this one does work too. And you can expand it easily for 3 or more fields

ColumnCreated= Text.Combine({[Author.FistName], [Author.LastName]}, " ")

[edhans · Accepted solution]

Use the following logic in Power Query:

= [Column1] & (if [Custom] = null then "" else [Custom])

It is critical you wrap the if/then/else construct in parentheses, or you'll get an error about a literal being expected. This will return the entire if/then/else as a literal, then allow you to concatenate with your other column.

Then mark that new column as text, and bring it into Power BI's DAX model.

[munchkin666]

@edhans worked for me. Thank you!

[edhans]

Great @munchkin666 !

[GMY]

It's work, but if you have several columns with null values it can became unreadable.

I create a function "IsNull" (sorry variables are in french)

let
    IsNull = (Valeur,RetourSiNull)=>
    let
        ValeurRetournee = if Valeur=null then RetourSiNull else Valeur
    in
        ValeurRetournee
in
    IsNull

You use like this

= [Column1] & IsNull([Custom], "")

I use this function to avoid null in calculus (IsNull([Custom],0) or comparaison (IsNull([Custom],"")="").

I hope this will be helpfull.

Guy

[vsolanon]

Thank you so much @edhans it worked like a charm 🙂

I am new in PowerBi and will start reading more about this properties to use 🙂

[edhans]

Great! Glad it helped.