Copy value format

[PVO3]

Hello community!

I'm struggeling with the feature copy value as shown in the (Dutch) screenshot below. 

€ 591.936 (measure datatype: decimal number, format currency, € 123, thousand seperator, 2 decimals)

€ 915.489 (measure datatype: decimal number, format currency, € 123, thousand seperator, 0 decimals)

No matter if I use the feature in Desktop or Service, the formatting of the pasted value is off.

When pasted in Excel the result is:

5919358600000100 

915489.18 

When pasted in Notepad the result is:

591935.8600000101
915489.18

How can I make the formatting be similar to the format shown in Power BI?

- Currency symbol not mandatory, but should still exist in measure

- Decimals not mandatory, but should still exist in measure

- Thousand not mandatory, but should still exist in measure

- Correct European number mandatory (so with , instead of . as decimal separator)

So the expected result is similar to the screenshot but at least:

591936

915489

If this is not possible I would like to disable the copy value feature in general.

Thank you all in advance!

[PVO3]

I tied all data types but without result.

Problem seems to be related to multiple measures and/or columns.

I can't recreate this problem in a new PBIX.

[Pragati11]

HI @PVO3 ,

Can you share the screenshot of the datatype that your measure has?

Thanks,

Pragati

[PVO3]

Hi @Pragati11 ,

Ofcourse I can. I mentioned it in my post, but see screenshot below.

[Pragati11]

HI @PVO3 ,

I tried the test at my end and it seems to work.

See below the tbale visualisation I have in Power BI with a totalPrice measure that is decimal type with 2 decimal places:

Now I try to copy the value of totalprice from 1st row in the visualisation and paste it to excel, I get it with decimal places as in Power BI Desktop:

The only issue I see here is:

1. In Power BI the measure is 2 decimal places
2. In Excel it is 3 decimal places - tghis could be something to do with the difference in the way formats are handled in Power BI vs Excel.

But I still get a decimal value in Excel.

Question for you is: What version of Power BI are you using as I am not able to replicate the issue at my end?

Thanks,

Pragati

[PVO3]

@Pragati11 thanks for al your effort!

I think I found the main problem.

Power BI seems to handle calculated columns differently. Basicly I had a calculated column which did somthing like Column A (calculated, with loads of decimals) + Column B (regular, loads of decimals). In Column A the datatype and decimals where not sufficient. I had to use a ROUND function otherwise PBI seems to be confused between decimal and thousand seperators. Weirdly enough I only found the 'Copy values' function to suffer from this.

So the last challenge is to get the 20729.982 from your screenshot pasted as 20729,98. But I'm afraid this is not possible.

Edit; this issue not only appears with calculated columns but also with measures. So basicly just DAX. For example I have a measure which in some cases adds 0.000001 to a measure. When I copy the value I get the incorrect result. If I change 0.000001 to 0 then I get the correct result.

[Pragati11 · Accepted solution]

HI @PVO3 ,

I am not getting any issues at my end with calculated measures. So not sure why you are getting it. The screenshots that I shared in my last responseis for a measure, and it works perfectly fine. 

I tried the similar thing for calculated column and it also works fine.

See below calculation for a column and in the visual:

When I copy th value in excel, I get it:

So, for me it is working for both calculated columns & measures. I am still keen to understand why you are seeing different behaviour at your end. I am using Power BI December 2021 update. What is your's?

Coming back to your next thing: So the last challenge is to get the 20729.982 from your screenshot pasted as 20729,98. But I'm afraid this is not possible.

I don't think, this can be achieved as excel has it's own formatting. So my understanding is when we copy something from Power BI to excel, it retains the actual value in excel. Which in a way makes sense to me.

Thanks,

Pragati

[HashamNiaz]

Hi @PVO3 !

Please try to place your measure into Card visual & separately into Matrxi visual and see if you can copy them with correct formatting.

Regards,

Hasham

[PVO3]

Hi @HashamNiaz thanks for your suggestion.

I'm sorry, but I'm not exactly sure what you mean. The card visual doesn't support 'Copy value'. 

I tried it with the table & matrix visuals. They both gave the same result.