Copying data from a Matrix into excell

[RonaldvdH]

Hey Guys, i'm hoping you can help me here

I have a dashboard containing a matrix and I want people to copy the data from that matrix and pasting it in excel.

That is not necesarrily the problem but when the data contains columns with a comma in them (for example a comment) then the result of pasting that data in Excell is that that 1 column in Powerbi are in fact 3 columns in Excel.

Is that something i can fix in the settings of powerbi or in the report?

Reproducing:

1. I right click on the value of a row to select everything below

2. I choose to Copy Selection

3. Open Excel and pasting the data

For example:

I have a column with a commment in it like "customer not at home, replan for next week, unless the call back"

Result when pasting that comment in excel is 3 column where the comma is like a delimiter

[v-mdharahman]

Hi @RonaldvdH,

As we haven’t heard back from you, so just following up to our previous message. I'd like to confirm if you've successfully resolved this issue or if you need further help.

If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. If you find a reply particularly helpful to you, you can also mark it as a solution.

If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.
Thank you for your patience and look forward to hearing from you.

[RonaldvdH · Accepted solution]

@v-mdharahman there is no solution to my problem so I had to fix it in an ugly way and that is to replace the , with a | (comma for a pipe)

[v-mdharahman]

Hi @RonaldvdH,

I'm glad you were able to solve your issue. Can you please mark the helpful reply as solution so that other community members can find it easy to solve their issues.

Best Regards,

Hammad.

[freginier]

Hey there!

When copying data from a Power BI Matrix and pasting it into Excel, columns containing commas (e.g., comments) are being split into multiple columns instead of staying as a single column. Excel treats commas as delimiters (especially in CSV-like data). When pasting, Excel automatically assumes a comma-separated format and splits data into multiple columns.

You can try this easy fix: 

Use Notepad as an Intermediate Step (Quick Fix)

Copy the data from Power BI.
Paste it into Notepad (this removes formatting).
Copy again from Notepad and paste it into Excel.

Hope this helps!

😁😁

[RonaldvdH]

@freginier I've tried your solution but unfortunately the result is the same.

The columns containing an , are still being split in excel when pasting the data

[freginier]

Hey, that's strange, I am not sure then. Maybe try  @Anonymous  solution! 

[Anonymous]

Hi @RonaldvdH ,

When copying data in Power BI and pasting it into Excel, commas can cause column separation issues. There is no direct setting in Power BI itself to resolve this issue, you could try the following methods:
1. Use a semicolon or other separator: In Power BI, when exporting data to a CSV file, you can choose to use a semicolon or other separator instead of a comma. This prevents Excel from mistaking commas for column separators.
2. Use Excel's Text Import Wizard: After pasting data in Excel, you can use the Text Import Wizard to manually specify separators.
3. Preprocessing data in Power BI: In Power BI, you can use DAX or Power Query to preprocess data and convert columns containing commas to other formats.
4. If you need to export data frequently, consider using Power BI's export feature to export the data to an Excel file. This ensures that the data is properly disaggregated when exported.

Best regards,

Lucy Chen

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

[RonaldvdH]

@Anonymous I'm not actually exporting the data because i'm using a Dashboard. I can only copy/paste the data into excel and then Excel 'thinks' the comma's are a delimiter. 

If there is no setting to change this behaviour the only option I have is to replace the , with an | (or something else)

The option mentioned by @freginier would have been a nice 'cheat' but unfortunately that didnt'work (just yet)