Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
vitexo87
Post Prodigy
Post Prodigy

Problem when exporting data to excel

I am exporting the data to excel of a power bi report, I am following the standard procedure however when opening it in excel many data do not respect the limitations and many special characters are being presented and are changed as for example in the image below:

Untitled.png

 

 

 

 

 

 

This problem is greatly impacting the activities, how can I solve this problem?

1 ACCEPTED SOLUTION

Select R visual and Drag all the columns require in exporting data to the  Values field . This will create a data frame called Dataset. 

 

As You can see in the screenshot, I have written R script.

write.table.........

 

Change the path of the file to your desktop.C;/Users.........

 

and Execute the script by pressing play button.

 

You will see the R file exported to your desktop.exporttest.PNG

Alternatively, you can use write.csv method as well which I have commented here. This will save your file in the default R directory. However, You can change the file path to save it on Desktop.

 

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

View solution in original post

11 REPLIES 11
BhaveshPatel
Community Champion
Community Champion

Hi @vitexo87

 

You can also use DAX Studio or R for exporting the data to excel. 

I can test the output if provided the sample file.

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

@BhaveshPatel

 

Could you give a practical example of how I could do this through DAX or R?

 

 

And how can I pass the data on to you?

You can upload your sample file to onedrive or google drive and provide me the link. 

 

I would like to see that whether the exact issue can be reproduced.

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

@BhaveshPatel

Do you need to extract the data in .xls or the data in its source in Power BI?

I would like to get both .xls file data source as well as powerbi file. 

This way I can test all three methods.

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

Hi @vitexo87

 

Sorry to get back you late. I have tried in Excel 2016 and R Environment both to export the data.

 

Excel 2016 comes with the predefined currency format but Export in R is clean and without any format.

 

If you would like to proceed using R, You need to install R Environment on your machine. 

I think this would be the most easy technique to export the data. 

 

You can find the file at 

https://drive.google.com/file/d/0B26iWnIZh6PSV0gzMXBveFZNN1E/view?usp=sharing

 

 

 exporttest.PNG

 

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

@BhaveshPatel

Thanks, but how do I use R on power BI? How will I export using R? I already have R installed on my machine.

Select R visual and Drag all the columns require in exporting data to the  Values field . This will create a data frame called Dataset. 

 

As You can see in the screenshot, I have written R script.

write.table.........

 

Change the path of the file to your desktop.C;/Users.........

 

and Execute the script by pressing play button.

 

You will see the R file exported to your desktop.exporttest.PNG

Alternatively, you can use write.csv method as well which I have commented here. This will save your file in the default R directory. However, You can change the file path to save it on Desktop.

 

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

@BhaveshPatel

Many thanks, last question, will this work in the cloud?

 

how can i resolver this error:

 

"Error Message:
R. Script Error
Error in is.data.frame (x): could not find the function "trim"
Calls: write.table -> is.data.frame
Execution stopped"

 

Untitled.png

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.