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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Nazdac911
Helper II
Helper II

In a List Columns , detect null values and replace them with a value

Hi all 

I have a couple of columns that contains Lists formed as follow : [ item1, item2, null , item3, .... ] 
those list values are connected to each others in different columns. 
When trying to expnd the llist using 

List.Transform

They work but I lose a lot of the rows and I recive this message ""Expression.Error: We cannot convert the value null to type Text""
How could I detect the null vallues and replace them with a nother value ? 
Thanks in advacne 

Regards 

 

1 ACCEPTED SOLUTION
AlienSx
Super User
Super User

Hello, @Nazdac911 

to replace nulls in your columns with lists 

= Table.TransformColumns(Source,{"col1", (x) => List.ReplaceValue( x, null, 0, Replacer.ReplaceValue)})

 

View solution in original post

5 REPLIES 5
Adamboer
Responsive Resident
Responsive Resident

 

Hi,

To detect null values in a list and replace them with another value, you can use the List.ReplaceNulls function. Here's an example of how you can use it:

Assuming your list is in a column called "ListColumn", you can use the following M code to replace null values with "NoValue":

 

let
Source = YourDataSource,
#"Replaced null values" = Table.TransformColumns(Source, {"ListColumn", each List.ReplaceNulls(_, {"NoValue"})})
in
#"Replaced null values"
This will replace all null values in the list with "NoValue". You can replace "NoValue" with any other value that you want to use.

vbnet
 

 

Hello, @Adamboer 

List.ReplaceNulls - new M function? Can't find it anywhere. 

AlienSx
Super User
Super User

Hello, @Nazdac911 

to replace nulls in your columns with lists 

= Table.TransformColumns(Source,{"col1", (x) => List.ReplaceValue( x, null, 0, Replacer.ReplaceValue)})

 

olgad
Super User
Super User

Hi, go to your table, Press Ctrl+A, it will allow you to select all columns, the right click replace values,  then use replace value feature from null to 0.

Hope you it helps. 


DID I ANSWER YOUR QUESTION? PLEASE MARK MY POST AS A SOLUTION! APPRECIATE YOUR KUDO/LIKE!
PROUD TO BE A SUPER USER!
Best Stories, Interesting Cases: PowerBI Storytime Newsletter
Linkedin Profile: Linkedin
YouTube Channel: PowerBI Storytime

Sadly this is not working , values are not changing 
Correction : data is represented curvy brakets : {1 , , 3 , 7 , 9 , , , , , 98 ,  , .....}
thanks in advance 
Regards 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors