Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
vsolanon
Frequent Visitor

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.

powerbi1.JPG

 

powerbi2.JPG

 

 

 

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!

1 ACCEPTED SOLUTION
edhans
Super User
Super User

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

9 REPLIES 9
sandermatthijs
Advocate I
Advocate I

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
Advocate II
Advocate II

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
Advocate I
Advocate I

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
Super User
Super User

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

@edhans worked for me. Thank you!

Great @munchkin666 !



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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

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 🙂

Great! Glad it helped.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.