R script and Python script not working after combining Excel files

[dbarrera]

I'm having troubles when I try to use R script or Python script in Power Query.

The source of the data are several Excel files that are located in the same folder. I used Power Query to combine this Excel files into one table and this is working fine. Now I need to use R Script or Python Script to create 4 more columns. The scripts work perfectly in R Studio and Jupyter Notebook respectively. 

However, the problem are not the scripts, since Power Bi is not letting me even to read the data. In both cases (R and Python) I tried something as simple as: df = dataset

And in both cases it resulted in an error. 

R script errror: 

DataSource.Error: ADO.NET: R script error.
Error in read.table(file = file, header = header, sep = sep, quote = quote, :
duplicate 'row.names' are not allowed
Calls: read.csv -> read.table

Python script error:

pandas.errors.ParserError: Error tokenizing data. C error: Expected 5 fields in line 3, saw 6

I would appreciate any help.

[dax]

Hi dbarrera,

I test this in my environment by using below script, it will work well

output <- dataset
output$new <- NA

So if possible, could you please inform me your R script sample and your expecting output, then I will try to test this in my environment.

In addition, in your error , it seems that you use duplicated column name, you could check and modify it. 

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dbarrera]

Thanks for your reply. 

As you can see in the following screenshot, my data does not have any duplicated column names

And my R script example would be:

df <- dataset

[dax]

Hi 

I test your sample df <- dataset, which work well in my environment. You said that the powerbi won't allow you read file, you need make sure you could access to the folder which contains your Excel or CSV files. When you combine Excel Files, will data  load successfully in powerbi(without apply R script)? In addition, you also could set privacy like below before use R script

Then you use R script to see whether it works or not. By the way, you also could download newest R from and apply this for powerbi to see whether this work or not. You could download  from  Revolution Open download page and refer to Create Power BI visuals using R for details.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dbarrera · Accepted solution]

Thanks for your help, 

The solution was post on this topic

It works for Python and R as well, I tried both.