SUM function cannot work with values of type String error

[purple_ninja]

Hi

I'm new to Power BI and trying to create some dashboards that show the sum of costs by category. I've followed the steps below but I cannot create a chart as I keep getting the error 'Function SUM takes na argument that evaluates to numbers or dates and cannot work with values of type string'.

In power query

1) pulled data from source,

2) converted all number columns to whole number

3) removed any error or null values with 0

4) loaded to workbook and checked fields - those that have come through as 'general' I have changed in the work book to number/date accordingly (why does it change it anyway??)

In Power BI desktop

5) Loaded data and created pie chart showing the categories

6) created a measure to calculate the total cost

7) attemped to create a bar chart to show costs by category.....

😎 Error message

In an ideal world I'd like to build the power query into the Power BI dashboard so I don't have to keep refreshing a second file - is there an eay way to load the connection?

[Anonymous]

Hi, 

I can't create a total column with the SUM function. I have the same problem, Power BI desktop tells me that it can't display the value through a visual.

Can you help me?

[v-yulgu-msft]

Hi @purple_ninja,

Please make sure the data you loaded to PowerBI desktop is in correct format, especially the cost column which should be number type.

If you have any question, please feel free to ask.

Best regards,
Yuliana Gu

[Anonymous]

Thanks! This solved my problem

[SofG]

Hi,

I have the same problem except that the data type is already "whole number".

The error message says: Calculation error in measure 'XX': The function SUNX cannot work with values of type string.

Is there another solution to this?

Thanks!

[KHorseman]

When you say you're loading them in Power Query, do you mean you're loading them in Excel using Power Query and then using Power BI Desktop to load from the Excel file? You can skip that. Power BI's query editor is the same as Excel's Power Query. You can just build your query in Power BI. In fact you could open the advanced editor in the Excel Power Query window and just copy the entire query as text into a blank query in Power BI if you don't want to rebuild your query from scratch.