Power Query and Power Pivot differ on duplicates

[macfuller]

Had an oddity that perhaps someone can explain.

I used Power Query to eliminate (I thought) duplicates in the data source for the column GHX Mfr Part #.

#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [#"GHX Mfr Part #"] <> null and [#"GHX Mfr Part #"] <> ""),
#"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"GHX Mfr Part #", Order.Ascending}}),
#"Removed Duplicates" = Table.Distinct(#"Sorted Rows", {"GHX Mfr Part #"})

I go into the Power Pivot data model and attempt to link tables with GHX Mfr Part # as one of the columns, but got the dreaded "The relationship cannot be created because each column contains duplicate values..."

Is there some better way to eliminate duplicates that Power Pivot will recognize?

 

[MarcelBeug]

Probably the issue is that Power Query is case-sensitive by default, while Power Pivot is not.
You can ignore the case when removing duplicates in Power Query, by supplying an additional parameter, e.g.:
#"Removed Duplicates" = Table.Distinct(#"Sorted Rows", {"GHX Mfr Part #", ComparerFromCulture(Culture.Current, true)})

 

[MarcelBeug]

ComparerFromCulture(Culture.Current, true)

Oops, forget a dot(.): Comparer.FromCulture(Culture.Current, true)

 

[macfuller]

Thank you!

I used the dropdown options (Excel 2013) for removing dupes rather than writing the code. I can certainly go into Advanced Editor to make your suggested changes, but is there an option on the dropdown that I missed to ignore case? I had no idea this was default behavior by Power Query (probably have to review my copy of M is for Data Monkey again!) and it seems pretty important.