Log In
Register
Home
May 2, 2014
Offline
I have a file that I have (successfully!) loaded into a PowerPivot model using Mynda's advice on using Connection only. However I have two columns that have date entries (these have been created with formulas from other data) and they are not showing up properly in the PowerPivot model. I've changed the format to date in both Excel and Power Query and still they are showing up as 31/12/3798 00:00:00 (instead of 11/7/2017) and 30/12/1899 00:00:00 instead of 11/7/2018. Any ideas?
July 16, 2010
Offline
Hi Anne,
Not without seeing what it looks like in Power Query. Can you share screenshots of the Power Query editor view, including the formula/M code you used to create the dates etc.
Mynda
P.S. the date format in the Excel worksheet won't be the issue as it goes from Power Query to Power Pivot, so somewhere between the two the date is not being translated properly.
May 2, 2014
Offline
So I have two columns that are not transferring properly...I've changed them both to Date/Time in PQ
1. Column is called: Contract Start Date. This is the formula. DATE(YEAR([@[S&M Renewal Date - Finish]]) -[@[Years subscription]], MONTH([@[S&M Renewal Date - Finish]]), DAY([@[S&M Renewal Date - Finish]])). The result appears as a normal date.
Works fine in Excel.
2. Second column: Renewal date: -VLOOKUP([@Name],Invoicing_schedule[#All],7,FALSE). Again works fine in Excel
Closed and loaded connection only to PowerPivot.
This is now what the dates look like: see attached screenshot. Grrr... thanks as always.
July 16, 2010
Offline
Hi Anne,
That looks like the Power Pivot screenshot. I need to see the Power Query view. After seeing your formula I think I need to see everything from beginning to end and screenshots aren't going to suffice.
Let's try this, can you please create a mock up Excel file with your dates in Excel calculating correctly and then load them into Power Query and calculate them in there, then load to Power Pivot so I can see the whole chain.
Thanks,
Mynda
May 2, 2014
Offline
Well DOH! on my part. I'm sending you a mockup of the actual file I use. The only steps I implemented were:
1. Unpivot other columns from I to CFO to normalise the dates.
2. Columns F and G both work perfectly in Excel. When I pull them into PQ, the results are still fine but they show up as data type "Any* - which I changed to date/time for both of them. It's only when I load into Powerpivot that it does the time travel thing!
3. Columns I to CFO - those dates show up fine.. Thank you so much 🙂
August 23, 2016
Offline
Hi Anne,
seems like its little bit strange, because if date in Excel showsing correctly and in PQ working fine after transforming to Date type its suppose to be working with in PowerPivot as well. just try to change format in PowerPivot for problematic columns: Home-Formatting-Data Type-Date (see snapshot). if you can share example of your data with working formulas (as it was mentioned in post above) it will be better because your file without PowerQuery information
July 16, 2010
Offline
Hi Anne,
Thanks for the file, however the problematic columns are formulas that don't evaluate unless I have access to the file Netfort_12.xlsx. Are you able to place the table being looked up in this file so I can see the format of the lookup data table?
Note: those columns F and G have a date format that adapts to regional settings, so I really need to see the lookup table as I suspect this is the problem.
Mynda
1 Guest(s)
