Forum Discussion
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
5 Replies
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.Sadly this is not working , values are not changing
Correction : data is represented curvy brakets : {1 , , 3 , 7 , 9 , , , , , 98 , , .....}
thanks in advance
Regards
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
