Removing Last 2 digits from sql database

[Jacques Naude]

Hallo
My problem sounds simple, but I am failing to resolve.
My data source is from SQL
I used LEFT and LEN functions in PowerPivot to get this 2 columns
However, If I format them, only the smaller amounts gets formatted correctly
See the 1000 separator, They are all formatted like the attached Picture

The original Columns which doesn't contain the LEFT and LEN functions, can format correctly

 

[Chihiro]

You can do few things.
1. You can write SQL query (edit source of the query) and use Convert() function or other appropriate data transformation funtion.



2. In Query Editor do one of following or all as needed
- Change column type to one of the appropriate numeric types
- Add calculated column.

EDIT: If going with method 1, you may need to do additional transformation at PowerQuery/Pivot level.

 

[David Evans]

PowerQuery should be able to help with this transformation and feed the data model. I've only used it sparingly, but this is one of the examples of use often suggested.