cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Python converts "NA" to NaN

Hello

I've recently started using Python when building queries for some more advanced data manipulation. However, it seems like Python (more specifically the Pandas library) automatically assumes the text value "NA" should be interpreted as NaN (i.e. a null value). This is problematic, for example, when working with country ISO codes, where "NA" represents Namibia. Example:

Original data:

2019-05-03 10_09_11-Untitled - Power Query Editor.png

Python script (empty, i.e. no manipulations taking place):

= Python.Execute("",[dataset=Source])

Resulting data:

2019-05-03 10_10_41-Untitled - Power Query Editor.png

From some quick Internet research, this appears to be standard functionality of the Pandas library, but can be avoided by supplying an additional argument when creating the DataFrame (see e.g. https://stackoverflow.com/questions/16596188/pandas-convert-na-to-nan). However, since Power BI creates the DataFrame before the script is executed, I can't prevent this unintended type conversion.

Has anyone encountered this problem before, or can anyone provide input on how to circumvent it?

Thanks in advance.

2 REPLIES 2
Highlighted
Community Support
Community Support

Re: Python converts "NA" to NaN

Hi @rthomsen 

A temperory method you may have a try:

Replace the value "NA" with another spefic value before executing python script in Power BI, 

After importing the data in Power BI, use Query editor to replace the value (Queries editor->Transform->Replace values).


Best Regards

Maggie

 

Highlighted
Frequent Visitor

Re: Python converts "NA" to NaN

Hi @v-juanli-msft 

 

Thanks for your response!

This does seem to work, but it is of course a bit of a hack, so I hope a more elegant solution could be found in the future. It does seem like Python/Pandas is the culprit here, but ideally Power BI would provide an option to bypass this strange behaviour.

 

Should I do anything more to report this, or is this post enough?

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Power BI Desktop August 2020 Update

Power BI Desktop August 2020 Update

We have great updates this month! Click the link for the video with more info.

Top Solution Authors
Top Kudoed Authors