Forum Discussion

dpeters65's avatar
dpeters65
New Member
5 years ago
Solved

Duplicate values after Group By

Refresh fails with message "Column 'Restart Cause' in Table 'new_sort' contains a duplicate value [redacted for company privacy reasons] and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table."

 

But this shouldn't be possible, because the Restart Cause column is the result of a Group By:
= Table.Group(Source, {"Restart Cause"}, {{"Count", each Table.RowCount(_), Int64.Type}})

 

By definition, Group By's should result in no duplications. As a workaround to this odd Power BI behavior, I added another step to remove duplicates, even there shouldn't be any:

= Table.AddIndexColumn(#"Removed Duplicates", "Order", 1, 1, Int64.Type)

= Table.Distinct(#"Sorted Rows", {"Restart Cause"})

 

Even after that I'm still getting the error during apply or refresh!

Full steps in case it's relevant:

= Table.Group(Source, {"Restart Cause"}, {{"Count", each Table.RowCount(_), Int64.Type}})

= Table.Sort(#"Grouped Rows",{{"Count", Order.Descending}})

= Table.Distinct(#"Sorted Rows", {"Restart Cause"})

= Table.AddIndexColumn(#"Removed Duplicates", "Order", 1, 1, Int64.Type)

 

EDIT: copy/pasted the wrong step.

  • Not sure if this applies here but when I see issues like this it is because M is case sensitive while DAX is not.  If you use UPPER or lower case of the column before you group, you will be sure to get rid of duplicates.

    Pat

2 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee

    Not sure if this applies here but when I see issues like this it is because M is case sensitive while DAX is not.  If you use UPPER or lower case of the column before you group, you will be sure to get rid of duplicates.

    Pat

    • dpeters65's avatar
      dpeters65
      New Member

      Thank you mahoneypat! I didn't realize that DAX is case-insensitive.