locked
Loosing Time format when importing an Power Query Table RRS feed

  • Question

  • Hope you guy can help me.

    I have a power query table which i get from our Exchange Server exporting the appointment data.

    So far it works fine...

    Now i want to consolidate that data in another power query for all our employees from a folder containing all the export excel files. Which works fine except for one detail. The time format reverts to am/pm format. I have tried to set the date format in the first query to "german" 24 h local setting but it doesnt help it always reverts back to am/pm. 

    if i do the import form a non power query table it works fine.

    I d be greatfull for any suggestions??

    Friday, April 15, 2016 9:12 AM

Answers

  • So I figured it out...

    do NOT use the one with the asterix on it (changing with system setting - See picture below). Altough my system setting is definitely set to a 24h clock power query will put it into am/pm format.

    I I use the one without the asterix it works fine...

    Daniel


    • Marked as answer by Dan798 Wednesday, April 27, 2016 8:08 PM
    • Edited by Dan798 Wednesday, April 27, 2016 8:09 PM
    Wednesday, April 27, 2016 8:08 PM

All replies

  • Hi Dan. Are you loading the Exchange Server data to an Excel sheet and then doing "From Table" to consolidate the data? If so, you should be referencing the first query in the second, rather than going to/from Excel.

    If this doesn't accurately describe your scenario, let me know.

    Ehren

    Tuesday, April 19, 2016 7:38 PM
  • Hey Ehren, That might be it. I am doing an folder import of the exelfiles. Won't I need everyone's login info if I reference the primary querys?
    Tuesday, April 19, 2016 10:04 PM
  • That depends. Are you importing the folders using multiple people's credentials?

    Or are you concerned that the people using the workbook would need to enter their credentials in order to refresh the query?

    Ehren

    Tuesday, April 19, 2016 10:28 PM
  • No I am just importing the data from the exel files. We actually have everyone export the data for their own time management. Afterwards they send those files to a central person who is supposed to import the data in a consolidated sheet. Do we need to get everyone's credentials in one query?
    Tuesday, April 19, 2016 10:35 PM
  • Ok, I think I understand now.

    What you're doing now (importing all the people's Excel files into a consolidated worksheet) is fine.

    Is the formatting issue you're encountering happening when the data is loaded to Excel? If so, you might experiment with the "External Data Properties", similar to what this post describes.

    Let me know if that solves your issue.

    Ehren

    Tuesday, April 19, 2016 10:44 PM
  • I ll try it on the weekend. But the formating issue is occurring when I consolidate the sheets not when I first get the data from the server. When the users export it from the server the format is fine Daniel
    Tuesday, April 19, 2016 11:35 PM
  • Ehren,

    I just tried to use the External Date Properties to keep the Format but it doesn't work. It still converts the format when quering the query...

    Wednesday, April 27, 2016 8:13 AM
  • Hi Dan. Hm. I'm not sure exactly where things are going awry. Could you post some screenshots of each step you're taking, along with what the data looks like when you've formatted it correctly in Excel, what External Data Properties you're using, and what the data looks like after you refresh?

    Thanks,

    Ehren

    Wednesday, April 27, 2016 8:04 PM
  • So I figured it out...

    do NOT use the one with the asterix on it (changing with system setting - See picture below). Altough my system setting is definitely set to a 24h clock power query will put it into am/pm format.

    I I use the one without the asterix it works fine...

    Daniel


    • Marked as answer by Dan798 Wednesday, April 27, 2016 8:08 PM
    • Edited by Dan798 Wednesday, April 27, 2016 8:09 PM
    Wednesday, April 27, 2016 8:08 PM