I am downloading custom files from Salesforce that have time stamped collection periods. It downloads in Day/time format down to the minutes. I re-format in Power Pivot, but I still get a lot of data that comes out as blanks which tells me the formatting isn't identifying with each other. I can't seem to find what I am doing wrong since I am formatting the date fields in the date table and Salesforce the same. If I form a pivot table with just the data, outside of power pivot, it doesn't have a issue. I have tried forming my own date table and also using the one available in excel 2016.
Any suggestions would be appreciated.
Hi Paul,
When you say you reformat in Power Pivot, what are you doing to reformat exactly?
Do you have a sample of the dates that are misbehaving that you can share. We only need a column of dates that includes some that get returned as blanks. If you can share a sample Excel file where you've loaded the dates into Power Pivot and they return blanks, then we can troubleshoot.
I suspect the dates are in the source as mm/dd/yyyy and your system is dd/mm/yyyy or vice versa, so please also tell us what your region settings are for dates and that of the SQL source.
Mynda
I would be glad to share both my excel file with power pivot and/or the separate individual files. Please let me know an email address to send it too.
Hi Paul,
You can attach it here. Click the 'Attachment' button at the bottom of the reply window > choose your file > click the yellow 'Start Upload' button.
Mynda
I realize that when I get comfortable with Power Query I will merge several of the year over year collection documents, but for now just trying to resolve the date issue.
I am including my Dashboard workbook that has all the documents in power pivot.
Also, two stand alone workbooks, 2017 collections and the date table I created.
Thanks and I look forward to seeing my errors.
Hi Paul,
Nothing attached. You need to click the yellow 'start upload' button to complete the upload process.
Mynda
Okay, sorry, trying again. I am in the US by the way. It seems my files are too large, can I email them?
Sure, or pop on Dropbox. Whichever is easier.
