Change the data type of an imported column from text to digits

[Anonymous]

Hi Everyone, 

I'm having an issue changing the data type of some imported columns from a dimension table to a fact table (with a *-1 relation). 

I've tried to import the columns with the RELATED DAX function, M functions, and also tried to directly merge the 2 tables (meaning just adding the relevant columns from the dim table to the fact table) 

Prices are formated as follows in the fact table : 4,01 while when imported they are 4.01 ==> Dot instead of a coma. 

Therefore I'm unable to do any calculations with these imported columns. 

I've tried to change the format of these imported columns from text to digits, nothing works as I get "Error" values. 

Does anyone knows a workaround ? 

Many thanks in advance for your help and time and have a nice day. 

Tom 

[HotChilli]

Ok, it looks like powerbi local settings or pc regional settings have the comma(",") set as decimal separator.  The  COUT_STANDARD field is a text field with a full-stop (".").  It looks like you want this to be a number type so you change the data type to decimal number on data view screen but that throws an error because powerbi doesn't know what to do with the full-stop - it's looking for a comma.  That's my interpretation.

-- 

I think my suggestion is to replace the full-stop with a comma (I would probably do this in Power query and change the data-type to decimal there too).  If you subsequently create the calculated column in the Fact table in DAX it should accept the decimal correctly.

---

Does the dimension table come from a different source than the rest of the data? Perhaps systems that use different regional settings?

Let me know what you think.

[Anonymous · Accepted solution]

Hi @HotChilli , 

Reverting back to you, what the IT team did was : 

1) Break the relationship between tables

2) Replace . by , in the prices table + modifying the format

3) Recreate the relationship between tables

4) Reimport the wanted colum values 

5) apply the calculation and we got the result 

So far it seems to work and I didn't have to change the regional settings. 

Hope this might be of use and many thanks for your time 🙂 

Tom

[Anonymous]

Hi @HotChilli ,

Thanks for your time, let's try it this way : 

1) I have to import prices from a table to another to make calculations

2) When importing the data (either with DAX or M language or by merging the tables) it creates a new text column as below : 

3) Here it gets picky, the imported column won't accept any format change action from text to digit (either through formulas or with buttons on the interface) like if I couldn't "copy-paste in values in excel"

4) Hence I'm getting "error" messages as below : 

I'm running a bit short of ideas and would appreciate any help 🙂 

Thanks

Tom

[HotChilli]

Happy to help but there is some confusion about what problem we are trying to solve here.

Can you repost with a briefer explanation please?