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.

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. 

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

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 🙂 

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

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