Power Query duplicate date error with no duplicates?!

macfuller

Active Member
Joined
Apr 30, 2014
Messages
317
Office Version
  1. 365
Platform
  1. Windows
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?
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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!
 
Upvote 0
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!
 
Upvote 0
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...​
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,304
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top