Forum Discussion
GroupBy DataFormat.Error
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.
Hi Anonymous ,
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
- Before the Group By step, insert a new Changed Type step.
- In the Power Query Editor, select the "Sum of Quantity Transactd" column.
- Click on Transform > Data Type > Decimal Number (or Whole Number).
- 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.
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.