Forum Discussion
Replace null(s) in a list
Hi!
The only replacement needed in your situation is {null, "NA"}, without quotes on null, and that's why your first code works and the second one doesn't.
Your function should work properly this way:
(others as list) =>
let
listA = List.ReplaceMatchingItems(others, {{null, "NA"}})
in
listA
I'm glad I could help you. See ya.
Hi Anonymous
Because in Power query, null means null value type, "null" mean a text field which its value is "null".
In your example, your data example show null value type, so the query with {{"null", "NA"}} doesn't work.
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Okay, with the cryptic documentation I was able find a function that worked. However, please explain why the “search text” and the “replacement text” requires duplication when only searching for one issue type (e.g. null)?
This format of the code works (all nulls are replaced):
listA = List.ReplaceMatchingItems(others, {{"null", "NA"}, {null, "NA"}})
This format of the code doesn’t work, (there are no errors however, none of the nulls are replaced):
listA = List.ReplaceMatchingItems(others, {{"null", "NA"}})
Here’s the complete function that works:
(others as list) =>
let
listA = List.ReplaceMatchingItems(others, {{"null", "NA"}, {null, "NA"}})
in
listA
Hi!
The only replacement needed in your situation is {null, "NA"}, without quotes on null, and that's why your first code works and the second one doesn't.
Your function should work properly this way:
(others as list) =>
let
listA = List.ReplaceMatchingItems(others, {{null, "NA"}})
in
listA
I'm glad I could help you. See ya.