cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper II
Helper II

Convert text to number

Hi everyone,

 

I am new to Power BI and this is my first attempt at uploading my budget and actual numbers. Because my budget is columnar, I needed to use the "Unpivot Column" option to sort out the Dates (which used to be the column header) and the amounts (which used to be the figures underneath the Dates header). Having done that, I then went ahead and changed the column type for both Dates (to dates types) and Amount column to "Fixed Decimal Number". 

 

By applying and closing, I am not able to get the budget figures and get the below error. Any help would be appreciated.

 

ThanksImage.png

7 REPLIES 7
Community Champion
Community Champion

Hi @ Jawed, This error might be because you chose to keep errors. Try removing errors and then change type of Budget Amount to Decimals
Regards,
Zubair


Thank you @Zubair_Muhammad. I deleted them and then tried it again with no luck. 

Community Champion
Community Champion

According to the data type symbol (123) in the column header, you changed the type to Whole Number instead of Fixed Decimal.

 

Personally I'm not in favour of data type Fixed Decimal (which is the same as currency), because it has 4 decimals.

 

I would prefer Decimal Number instead, and make sure that any currency amounts are properly rounded.

 

Type Decimal Number.png

Specializing in Power Query Formula Language (M)

Hi @MarcelBeug. I tried that option as well. Somehow it is not picking it. Is having zeros in the cells going to make the difference? When I am on the Query Editor, I can see the numbers but, when I "Close & Apply" and come out of it, the numbers simply disappear. I may be doing something wrong but dont seem to think of anything at this moment.

Community Champion
Community Champion

In general, when you have values in a column that do not comform to the column type, the values will be visible in the Query Editor, but not after loading the data.

 

Another possibility would be that you need to transform the data type using Locale, e.g. if the text values have decimal points, but your culture has decimal commas.

Specializing in Power Query Formula Language (M)

Thank you. I will have to run some forensic tests to clear out the data. Probably worth to round the numbers in excel to ensure there is nothing hidden. Does it make any difference if the zero appears as (0) or (-)?

Community Champion
Community Champion

No, the number format (0 or -) in Excel makes no difference for Power Query.

Specializing in Power Query Formula Language (M)

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors