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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors