Forum Discussion

vsolanon's avatar
vsolanon
Frequent Visitor
6 years ago
Solved

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 ...
Need Help
Tips and Tricks
  • edhans's avatar
    edhans
    6 years ago

    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.

edhans's avatar
edhans
Icon for Community Champion rankCommunity Champion
6 years ago

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.

GMY's avatar
GMY
New Member
3 years ago

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