Text to Decimal

Anything related to PowerPivot and DAX Formuale
Posts: 27
Joined: Wed Feb 25, 2015 4:51 am

Text to Decimal

Postby leimst » Thu Feb 01, 2018 6:26 am

Good Afternoon,

I have a column in Excel that contains text and numeric data. I have Power Pivot linked to this table but found that it has set this particular column to text and will not allow the column to be changed to a numeric data type. This is problematic since I would like to sum the numbers that are present in that column. Is there a way to cause my DAX formula to look only at the numbers for summing while ignoring the cells that contain text?

Thank you in advance,


Posts: 922
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Text to Decimal

Postby MattAllington » Thu Feb 01, 2018 6:44 am

Well you can't load data into a column in Power Pivot that has text and numbers AND have it marked as a numeric column. Same with any database actually. But DAX is a very forgiving language. If you were to add a Calculated Column to your table something like =IFERROR(VALUE(Table1[Text]),BLANK()) you may get what you need.

Or you can use Power Query to remove the text before loading
Matt Allington is Professional Self Service BI Consultant, Trainer and Author of the book "Learn to Write DAX". You can hire me at http://Exceleratorbi.com.au

Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 10 guests