Power Query duplicate date error with no duplicates?!

[macfuller]

Excel 2016 64-bit - Suddenly my calendar file that loaded just fine is throwing errors that there are duplicate dates, so it can't join to the other DAX tables. The query appears just fine in PQ but blows up when I try to load to the Excel data model.

These lines at the end don't make a difference

RemoveBlanks = Table.SelectRows(InsertedStartofWeek, each [Date] <> null and [Date] <> ""),
RemoveDupes = Table.Distinct(RemoveBlanks, {"Date"}),

Anyone else have this behavior? Solution?

 

[macfuller]

No solution. I needed to add a column and expand the dates, so I created a new query with the same M that failed in the existing date table. It built the relationship to the other table with no problem.

So I had to rebuild the model to have everything point to the new date table, new Sort By column setup, replace all instances of the old date table with the new in slicers, timelines, rows and columns, redirect measures and calculated columns, delete all the old date relationships, and finally delete the query.

I hope there's an easier way that I didn't think of, since this might happen again!

 

[Rukt]

Sorry to necro-post, but this is the only page I ever found that mentioned this error, and I wanted to share the reasonably quick fix that worked for me:

1. In the Power Pivot Diagram view, delete relationships between the Calendar table and any fact tables (for me, this was three one-to-many relationships between corresponding [Date] columns)
2. Even if there are measures in other tables that reference the Calendar table, leave these as they are
3. In Power Query, add a step to the end of the Calendar table to "keep the first 0 rows" (i.e. remove all rows, = Table.FirstN(previousStep, 0))
4. In the main Excel window > Data ribbon tab > Queries & Connections pane, refresh at least the Calendar table (the Calendar table successfully refreshed at this point, although all the pivot tables were empty or wrong because of the missing relationships)
   • I seem to recall trying to refresh all at this point, although I believe this raised a different error, and may not be necessary)
5. Back in Power Pivot, re-establish the relationships that were deleted in step 1
6. Back in Excel, refresh all

This meant that I did not need to create a new query, update slicers, update measures, etc. Good luck, future readers!

 

[Rukt]

Oops, obviously I forgot to include the step where I removed the temporary Table.FirstN step from the Calendar query. I suspect that it belongs before step 5, so:

​

4b. In Power Query, delete the "Keep the first 0 rows" step from the end of the Calendar query. Save & load, then refresh the query again in Excel for good measure...​