cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mmarino
Regular Visitor

Replace null(s) in a list

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:

 Power Query List View.jpg

 

2 ACCEPTED SOLUTIONS

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.

View solution in original post

Hi @mmarino 

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.

View solution in original post

5 REPLIES 5
mmarino
Regular Visitor

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 @mmarino 

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.

View solution in original post

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.

View solution in original post

bluedatenbrian
Advocate II
Advocate II

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
Community Champion
Community Champion

@mmarino 

 

Hi, try using this in a custom column:

 

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

Regards

 

Victor




Lima - Peru

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors