Help needed importing custom formats from Excel column that were manually entered on each cell

[nicoenz]

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

[ryan_mayu · Accepted solution]

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

[ajohnso2]

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

[nicoenz]

@ajohnso2 

thanks!!!! i've been trying that also but the owner of the report can't find that key in SAP.    😞
I'd like to know if there's a trick within PowerBI to do it

[ajohnso2 · Accepted solution]

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)

[nicoenz]

@ajohnso2 in fact. there is someone adding it manually. that's when i said that i could try to do it in pbi.

yes. it is SAP system so the units of measure must be somewhere.

Thanks for your support

[nicoenz]

hi @ryan_mayu ,

there's no such a step. the info is not even visible when selecting the .xlsx as a source.

[ryan_mayu]

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=10... 
thanks again

[ryan_mayu]

can't open the link you provided, it shows unauthorized.

[nicoenz]

@ryan_mayu oops. sorry.

please try again

https://docs.google.com/spreadsheets/d/1QYdCHCZMDtu6OSCGjAUt2E8TgK0rdpIL/edit?usp=sharing&ouid=10258... 

[ryan_mayu · Accepted solution]

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

[ryan_mayu]

@nicoenz 

maybe you can check in PQ to see if there is a step called "changed type". Delete that step to see if the format is correct.