none
data update bringing in different date formats RRS feed

  • Question

  • I am using PowerQuery to perform an API data download from my vendor.  The problem I am having is that the date fields are not importing correctly.  The original data is in a datetime format.  I have chosen to convert this to a date format in the PowerQuery.  When I open the Excel file with the updated data, most of the rows are correctly formatted as date, but the last few rows have 'general' rather than 'date' as the cell format.  I have updated the spreadsheet and formatted the date columns with the date format I want, but somehow with each PowerQuery update the last few rows keep reverting to a general format.

    If I had 5000 rows of data and I run the update and retreive 5100, it looks like the newest 100 will be formatted incorrectly.  All other columns appear as expected, it just seems to be the dates that are changing.

    Any ideas or suggestions?

    Friday, March 13, 2015 5:40 PM

Answers

  • Hi richtheh,

    Can you share a screenshot of the worksheet with the wrong format?

    If you unload the data and load it again do you still have this issue?

    To unload and load the data again follow these steps:

    From Workbook Queries pane, right click the specific query, select "Load To...", uncheck Load to Worksheet, click OK, and repeat again these actions when the load to worksheet option is checked.

    Thank you,

    Gil

    Saturday, March 14, 2015 8:25 AM

All replies

  • Hi richtheh,

    Can you share a screenshot of the worksheet with the wrong format?

    If you unload the data and load it again do you still have this issue?

    To unload and load the data again follow these steps:

    From Workbook Queries pane, right click the specific query, select "Load To...", uncheck Load to Worksheet, click OK, and repeat again these actions when the load to worksheet option is checked.

    Thank you,

    Gil

    Saturday, March 14, 2015 8:25 AM
  • Did you try explicitly changing the data type of that column to Date as a step within Power Query?
    Saturday, March 14, 2015 10:49 AM
  • Hi Gil,

    Unfortunately, I already overwrote the Excel files with the 'general' formatting so I cannot provide a screen shot.  Over the weekend as I was looking at the files, I realized that while I thought I was formatting the full column, I was actually only formatting the newly uploaded data with a true date format.  I went back and reformatted each complete column (rows 1 - 1M+) on each sheet where I was importing a date field and it looks like that solved the issue.  If it appears again, I will try the unload and load again as you suggest.

    It seems odd that it is an Excel formatting issue as I would expect the PowerQuery result to override any pre-existing spreadsheet formatting.

    Anyway, so far so good.  Thank you for the suggestion!

    Richard

    Monday, March 16, 2015 11:27 PM
  • Hi Bob,

    Thanks, yes I had changed the data type of each column to Data.  They were originally datetime but I don't need the time component so changed everything in the PowerQuery to Date.

    Richard

    Monday, March 16, 2015 11:28 PM
  • I did find an uncorrected file as an example.

    Monday, March 16, 2015 11:54 PM