none
Why so difficult to work with Date/Time in Power Map?

    Question

  • I have a SSAS model that I am reading data from into a pivot table, I then have to copy and paste that data into a separate worksheet (since Power Map doesn't seem to support connecting to pivot data), and then have to take my date value and do a multiply by 1 trick to get any of the dates to be recognized as dates in power map. All that still appears to be for naught if I have already connected to that particular table without doing the paste/multiple dance, as it still won't detect the value as a date in Power Map.

    I've also noticed that the various layers, though deleted in Power Map, never seem to disappear. How does one purge that information from the spreadsheet for good? I've tried deleting the layers, I've tried deleting the tours, but it always shows every range or table I have ever added data for.

    Thursday, July 25, 2013 8:12 PM

Answers

  • Hello,

    Power Map leverages the Excel Model (xVelocity engine) in Excel 2013 as its data backend.  As such, it needs to be identified as a datetime type within the model.  You can check this with PowerPivot.

    We are aware of the difficulties of the model recognize datetime types from SSAS and SQL.

    Regarding deleting tables from Power Map, the easiest way to do this is to remove the data from the model.  Power Map reads all of the data from the Excel Model.

    Hope this helps,

    Kevin (MSFT)

    Sunday, August 18, 2013 6:32 PM

All replies

  • Doesn't seem that the Power BI features are particularly well supported either. That's a shame as I would think it would be pretty crucial while introducing new tools to developers to provide some sort of support for them. 
    Tuesday, August 06, 2013 5:20 PM
  • Hello,

    Power Map leverages the Excel Model (xVelocity engine) in Excel 2013 as its data backend.  As such, it needs to be identified as a datetime type within the model.  You can check this with PowerPivot.

    We are aware of the difficulties of the model recognize datetime types from SSAS and SQL.

    Regarding deleting tables from Power Map, the easiest way to do this is to remove the data from the model.  Power Map reads all of the data from the Excel Model.

    Hope this helps,

    Kevin (MSFT)

    Sunday, August 18, 2013 6:32 PM
  • I'm having difficulty with dates too in Power Map, I wonder if it's not to do with regional date settings where the ISO standard and the world goes with 2013-09-05 and the USA is still stuck in the 1950's with 09-05-2013, month and date being swapped around.

    I'm also confused with all the left over data sets. Does this mean one has to go into Power Pivot to remove the ones you don't want?


    Sign here

    Thursday, September 05, 2013 1:31 PM
  • It sounds like you at least are able to get Dates to be recognized. Could someone shed some light into the best way to do this? I've tried a bunch of formats but PowerMap still wont recognize the column as a time column. Please help. thanks.
    Thursday, September 12, 2013 4:22 PM
  • See the summary section of http://support.microsoft.com/kb/214094 - it explains about how Excel stores dates and times. Essentially, starting with January 1, 1900, each full day adds 1 to the date serial number. The times are a decimal fraction of the date, so for instance, today is 9/30/2013, with a true value of 41547. Excel is smart enough to know that when you enter 9/30/2013, you are entering a date, and it enters 41547 and formats that as a date. But if you enter that in one column, and 12:00 in another column, it has no way to tie the two together. To Excel, 12:00 PM means .5 and without the date, it just means January 0, 1900 at 12:00 pm, which isn't going to work for plotting data. Try entering your dates and times in the same column, so Excel can make sense of the combination.

    Hope this helps!

    Anita Oakley

    Monday, September 30, 2013 10:42 PM
  • I'm not getting any sense with dates either. Is it essential to use American formatting or not?

    I'm using dd/mm/yyyy hh:mm:ss but it is not working. Can anyone give me a format that definitely works?

    I would be most grateful as I am losing time on my project.

    Cheers, Karl

    Friday, November 08, 2013 11:54 AM
  • Have discovered why - Windows thinks the world began on the First of January 1900 and my date column has values in from before then.

    Can't see a workaround except to add 1,000 to the year and add a note on the screen. Crap.

    Sunday, November 10, 2013 5:29 PM
  • There actually is a trick that works to make dates before 1900 work in Power Map. The trick is to flag the column as a Date column using Power Pivot.

    It's intuitive to think of Power Map as part of Excel, and it is a part of Excel, but the actual data source for Power Map isn't Excel per se, it's the Excel workbook's xVelocity data model which is shared between Excel, Power Pivot, and Power Map. So you can edit Power Map's data model using Power Pivot.

    I hope this helps!

    Blair Neumann (MSFT)

    Monday, November 11, 2013 5:10 PM
  • I'm not sure if my problem is related or not. I don't have any dates prior to 1900, but Power Map doesn't let me associate my date column with the time dimension. My dates started off as strings (e.g. "11/24/2013 4:00:00 AM"), but I declared them as 'Date' cell format in excel. I also tried changing the column to 'Date' in the PowerPivot model management UI. No joy.

    Any tips? Thanks.

    Tuesday, November 26, 2013 11:37 PM
  • I think I figured out the incantation:

    - Mark the text field to Date in PowerPivot model management UI

    - Save

    - Close out of excel, PowerPivot PowerMap et al

    - Re-launch excel and create new map

    HTH someone.

    Wednesday, November 27, 2013 12:47 AM
  • I created a datetime column in powerpivot, but the Time animation field isn't accepting it being dragged in. Date alone work but my data has time also. What's the trick?
    Saturday, January 11, 2014 5:51 AM
  • I found that if I formatted the datetime as UTC that it was accepted by the Time field in Power Maps
    Saturday, January 11, 2014 6:31 AM
  • Just started out with PowerMap. I'd like to use it even without another Power tool. I can get most features to work except for time. I've tried setting my data up in an Excel spreadsheet every possible way. Typing in dates like 1/1/2014, typiing in numbers like 41640.00, typing in formulae that take typed dates and add 0 or 1, typing dates in Quotes... Nothing allows me to select my time column as "time" in PowerMap.

    This should be very straightforward. What's the issue?
    Thursday, February 06, 2014 6:22 PM
  • Ok, I see I need to "format" the cells that contain the dates so that they follow a Gregorian convention 1/13/2014  .... wow, is it just me or is this kind of crazy?
    Thursday, February 06, 2014 6:59 PM