GroupBy DataFormat.Error

[allarsen]

I'm trying to do a groupby operation, and am getting the following error:

That was never supposed to be a number, in the previous step I specifically converted it to text:

What gives?

[allarsen]

@SacheeTh 
@lbendlin 
The [Sum of Quantity Transaction] column isn't the column with the error. (I did a remove errors on the column, just in case).

The item it is referring to '342770207122E' is in the "Current Item Number" column, which should not be numbers, it should be strings. That 'E' is not a mistake, it is in the part number. So I can't remove it from the dataset, and can't covert that column to numbers. It shouldn't have to be numbers either, the same way "Branch Plant" isn't numbers. No math is being done there.

[SacheeTh]

Hi @allarsen ,

I'm giving you a step by step sujjestion for @lbendlin answer.

• The column "Sum of Quantity Transacted" might have some values stored as text instead of numbers.
• Even though it looks like a number, Power Query may have interpreted it as text in earlier steps.

Soluton: Convert the column to a number before grouping

To fix this, follow these steps:

Opton 1: Convert the column explicitly to a number

1. Before the Group By step, insert a new Changed Type step.
2. In the Power Query Editor, select the "Sum of Quantity Transactd" column.
3. Click on Transform > Data Type > Decimal Number (or Whole Number).
4. Make sure the column is correctly converted before proceeding with the Group By operaton.
   
   

Try this one as well: If some values are causing errors, modify the Group By formula like this:

= Table.Group(#"Changed Type", {"Current Item Number", "Branch Plant"},
{{"Sum", each List.Sum(List.Transform([Sum of Quantity Transacted], each try Number.From(_) otherwise 0)), type nullable number}})

// And You can check if your column has text values by adding a Custom Column with this formula by 
= Value.Is([Sum of Quantity Transacted], type text)

If it returns TRUE for any row, that means some values are stored as text.

[allarsen · Accepted solution]

I had to change the "Current Item Number" column to text at the initial import, not in a later step (but still before the groupby operation). I don't know why.

[lbendlin]

your column [Sum of Quantity Transacted] has a data quality issue, including non-numeric values.