Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
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!
Solved! Go to 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingMaybe 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
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.
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]}, " ")
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingGreat @munchkin666 !
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingIt'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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCheck out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.