Forum Discussion
Helper IIIHelp needed importing custom formats from Excel column that were manually entered on each cell
Hi I have a column in a BusinessWarehouse export Excel report that has production costs. The format is custom and applied to each cell (rows 2, 3, 4 are formatted as "0.00 CAD", row 5-9 "0.00 MXN", etc). When I import the column in PowerBI the format dissappears and it just brings a number without units. I can't work with conversion rates then.
I want to import these numbers in PowerBI and retain the format. Do you have any solution to this?
Regards and thanks
nico
| Production cost |
| 100.00 CAD |
| 500.00 CAD |
| 200.00 CAD |
| 2'000.00 MXN |
| 6'000.00 MXN |
| 3'000.00 MXN |
| 4'500.00 MXN |
| 5'000.00 MXN |
| 250'000 JPY |
| 100'000 JPY |
| 150'000 JPY |
| 7'500.00 EUR |
| 8'000.00 EUR |
| 6'000.00 EUR |
| 4'000.00 EUR |
The currency will not import to POWERBI is because it is a custom format.
it looks like 1000.00 USD. However, it is still 1000 in the formula bar.
e.g , the cell shows 999, but actually it is 1.
so when you import it to PBI, it will show 1, not 999.
Maybe you need to find other approaches like creating a new column
Theres no tricks.
You just need to identify the format to use, I do not know your data, if this data is from some kind of financial system I would expect a currency attribute somewhere.
(I would hope someone isn't manually adding currency formats at excel level row by row?? :D)
11 Replies
ryan_mayuSuper User
could you pls provide some sample data ? or maybe change the custom format to TEXT and try again?
- nicoenz
ryan_mayu Pasting the data as text doesn't work. it just copies the number w/o the currency.
here's the link https://docs.google.com/spreadsheets/d/1QYdCHCZMDtu6OSCGjAUt2E8TgK0rdpIL/edit?usp=drive_link&ouid=102582693328620678626&rtpof=true&sd=true
thanks again
ajohnso2Solution Supplier
You can apply a dynamic number format to your measure that will sort this.
Please note, you will need some mechanism to identify which format to use - ideally this could be added as an extra column on your source data, it could simply be the format e.g. 0.00%,(0.00%).
Refer to the following document.
Create dynamic format strings for measures in Power BI Desktop - Power BI | Microsoft Learn
- ajohnso2
Theres no tricks.
You just need to identify the format to use, I do not know your data, if this data is from some kind of financial system I would expect a currency attribute somewhere.
(I would hope someone isn't manually adding currency formats at excel level row by row?? :D)
