Power BI csv import creating blank data

[Saarek]

I have a dataset that is created within R and then exported out as a single CSV file for Power BI to ingest.

This data set is 36 columns wide with, approximately, 6 Million Rows. Every row/column is populated, there are no blank/null values.

When Power BI imports this data there is a small percentage that it seemingly random assigns blank values too. Not the entire row or column, just random blanks here or there throughout the dataset. By a small percentage, one of the columns has 956 blank entries so an error rate that's effectively a rounding error, still, as the data is complete in the source file it should be complete in the imported data set to.

I've verified the integrity of the CSV file by loading it back into R, where it is complete. I have also loaded the file into Python, once again the data was fully populated.

As this dropping appears to be random and effects all data types (text, number) I'm at a loss as to how to force Power BI to read the file in correctly.

Are there any known issues surrrounding the import of large CSV files into Power BI, any work arounds?

[Anonymous]

Hey, I know the thread is old, but just to add on, I agree with OP that PowerBI is doing something weird with `NA` values in csv files. I had the same issue, with the imported file reading in NAs as text rather than `null`, messing up the data types and making edits in power query nigh impossible. 

The only solution I found, and I hate this, is to use `writexl::write_xlsx()` for the export from R. Then all `NA`s were correctly picked up as nulls. Seems like Microsoft has created an artificial barrier to privilege excel files and prevent the use of non-proprietary formats. Separate issue, but I also hate that they charge USD 10 a user just to view R visuals that are free and open source. 

[v-easonf-msft]

Hi, @Saarek 

PowerBI seems to have a limit on the size of Imported/Exported csv files.

You can refer to the solution mentioned by @v-yuezhe-msft in this post, use VBA code or other online tool to split the imported CSV file into different worksheets, and then import the CSV file into Power BI.

Best Regards,
Community Support Team _ Eason

[Saarek · Accepted solution]

Yes, no matter what I did I could not get it to work via CSV import. In the end I created a basic R Script to import an RDS file (R Native file format) and it loaded fine.

Obviously something wrong with the CSV import process in Power BI for this file, but I just couldn't find the issue and squash it.

[parry2k]

@Saarek very hard to say but ultimately it is your data file that is causing the issues. Good luck!

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[parry2k]

@Saarek not aware of anything, just make sure the data type of the column is correct, if there is a data type let's the number and then value turns out text, might run into issues.

also does values has delimiters? Maybe add and then during import in power bi, put delimiter to make sure there is no extra comma in the value which is causing this.

finally, you can try to use power bi dataflow and see if it works there but I will first perform previous steps.

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[Saarek]

Thank you for your advice. I think I have potentially found the column that is throwing Power BI off. But, I cannot see why.

The column is formatted as Character within R and is being imported as Text within Power BI, which would be correct.

One of the problem values seems to be "28.63". I cannot change this column to a type of number because some of the entries are not compatibile with that, example "17/10.5". What I don't understand is why 28.63, along with about 6 other variations, fall over when all of the other hundreds of similar looking combinations pull through just fine.