Forum Discussion
Copying data table from Power BI/Query to Excel
Hello,
I have transformed by data using Power Query and am trying to copy the data to Excel by loading it to Power BI, right clicking on the table in the list, and selected "Copy Table". When I try to Ctrl+V, I get an error "Problems During Load" because of "Style". So when I try to paste just as text, it works, but then I noticed that there were some rows added in that were blank excel for one column of data.
Investigating this, I found that one of my columns contained some cells that had carriage returned added to them like below. I assume this is what caused the issue when trying to paste, and then when trying to paste as text, it treated each of these as a different row rather than a single string of text.
So my question is, how do identify and remove these carriage returns so I don't have this issue?
One Two Three |
Hi cah2035 ,
I test this in my environment, which could work well. You could click like below, then paste this in Excel.
By the way, I think Analyze in Excel is more like a add-in tool which help open or connect dataset from powerbi service and analysis this in Excel. You could refer to service-analyze-in-excel for details.
Best Regards,
Zoe ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
Hi cah2035 ,
I test this in my environment, which could work well. You could click like below, then paste this in Excel.
By the way, I think Analyze in Excel is more like a add-in tool which help open or connect dataset from powerbi service and analysis this in Excel. You could refer to service-analyze-in-excel for details.
Best Regards,
Zoe ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Sometimes with this option it will only copy paste the first 1.000 rows. Is there a way to copy paste entire table? IF for example I have 45k rows.
This is a very old post. Power Query in Excel is now very close to feature parity with the one in Power BI. No need to copy/paste the data any more - copy/paste the query.
You can remove them in Power Query.
Either use Character.FromNumber(10) or the much more prosaic "#(lf)"
https://docs.microsoft.com/en-us/powerquery-m/character-fromnumber
The actual question is - why are you using Export? What made you not consider Analyze in Excel?
lbendlin Thanks for the help, I will try this out.
What is the "Analyze in Excel" option?
Edit: Is this an option for a visualization? I am not actually using Power BI for visualization. I am using it for Power Query since it has better function than Power Query in Excel, except for when trying to get the data into an excel spreadsheet.
