Issue with excel-pasted tables from "Copy Table" in power bi (version 2.93)

[CBO]

Hi,

I researched the issue without much luck. Essentially I want to export data from a pbix file to excel. Only interested in the "raw tables" (no need for additional DAX-measures). I used the "Copy Table" (available on right click) and was confronted with the following issues:

1. All the appropriately defined fields got copied as text data-types

2. As I tried to manually fix my Excel, the find-replace command would not work, even though the worksheet isn't protected.

Has anyone else observed the same? Is this a known issue. Any alternate approach.

Thanks in advance for your advice.

CBO

[v-angzheng-msft]

Hi, @CBO 

I don't have this issue with the same version, it works fine for me. Maybe this is a coincidence or maybe it has something to do with your application environment.
Please try to create a blank Excel file and retry copying the table to see if the issue goes away.
Or use other ways of exporting data, for example, exporting data from a visual object, exporting data from a selected table in the PowerQuery editor, running a query using DAX Studio and then exporting the data, exporting data from Service using the analysis feature in Excel, choose the one that works for you.

my version:

refer:

Exporting Data from Power BI Desktop to Excel and CSV – Part 1: Copy & Paste and DAX Studio Methods

Exporting Data from Power BI Desktop to Excel and CSV – Part 2: Importing Power BI Data Directly to ...

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[CBO · Accepted solution]

Hi,

sorry for the late answer. I wanted to get my hands on a different environment just to test your comment.

A few more insights:

Background:

The considered table is not small (350,000 lines, not many columns)

The problem only affects date and whole number data types, which are not exported as their power BI data type but as Excel General, i.e., pretty much text.

Findings:

1. It takes a few seconds to copy the table, and trying to paste it too quickly seems to mess things up. 

2. What is seemingly a space-separator for the thousands in whole numbers, is not the same characters as the space bar in Excel, hence my struggle in the find-replace (space bar in excel is "U+0020 : SPACE [SP]", what got exported "U+00A0 : NO-BREAK SPACE [NBSP]")

Workaround:

The idea is to reformat in Power BI (both the date and whole numbers) as close as possible to a text format I can easily turn into the desired format

- for whole numbers: remove any separators (no non numeric characters): in that case, it gets identified as a number

- for dates: same idea, no text something like mm/dd/yyyy, and then use the text to column conversion in excel

Conclusion:

We spend a lot of time rightly assigning data types and adequately formatting columns in power BI, it would be logical and practical that the copy table function in power BI preserves the data types when exporting to a software under the Microsoft umbrella.

[CBO]

Hi Amit,

thanks for having a look at the question. You're right and I forgot to mention I did try to change directly in Excel the data type (both using the cell data type and the "Text to Column" functionality. As both of them failed, I undertook to replace the white space that separated 000 in a number column (hence the find replace) to make the data more "number-like", but that failed too. As if the data was locked, but apparently it wasn't.

Thanks

CBO

[amitchandak]

@CBO , If you have used copy Table and then paste on Excel. And if the data type so not match then select that column and correct the data type.

Not sure on the use of find and replace