Forum Discussion
Power Query combining NULL values
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!
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.
9 Replies
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.
Great! Glad it helped.
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 IsNullYou 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
edhans worked for me. Thank you!
Great munchkin666 !
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]}, " ")
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.
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
