Replace null(s) in a list

[Anonymous]

I’m new to M language and I need help regarding converting JSON data using Power Query. As a whole, the process is working however when a “null” is encountered such as shown in the “list” view below, and the “Extracted Values” function is invoked, not all indexed rows (13) are included in the output string (only 12 rows are past to the string). Therefore, when the Split Column by Delimiter (;) function is invoked the data in the converted table doesn’t aligned properly under the respective headers.  To further complicate things, the nulls locations are random and can be more than one.

Question: Is there a way to replace all “nulls” with a value such as a -1?  This would retain all 13 rows in the transformation process.  I have tried a few of the “list” functions but I could not get any to work… 

Any examples would be greatly appreciated!

Example list view:

 

[bluedatenbrian · Accepted solution]

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.

[Anonymous]

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

[v-juanli-msft · Accepted solution]

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.

[bluedatenbrian · Accepted solution]

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.

[bluedatenbrian]

HI mmarino,

It has a try-otherwise statement available and may it help you. Like: try Extracted Values[...] otherwise null.

But I think if you build a function to extract that list, you'll solve your problem:

Choose one of this lines and click on List to expand only that list. Then you can replace null values. When you have it done, duplicate your source and convert it to a function by adding "(var as <type>) =>" and make the first one use it.

[Vvelarde]

@Anonymous 

Hi, try using this in a custom column:

List.ReplaceMatchingItems([Column1.values],{{null,-1}})

Regards

Victor