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

[Nazdac911]

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 

[Adamboer]

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

[AlienSx]

Hello, @Adamboer 

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

[AlienSx · Accepted solution]

Hello, @Nazdac911 

to replace nulls in your columns with lists 

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

[olgad]

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. 

[Nazdac911]

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