Hi all,
just recently stumbled into problems with data import from an Excel-sheet into PowerQuery (PQ).
The excel sheet carries several columns, some containing durations calculated within the sheet (formatted more or less "[h]:mm:ss"). Now I want to import some of the columns into PQ to do some calculations. [case 1:] But PQ won't import the duration columns in 'the right way', they are imported as datetime columns. Changing to duration doesn't work, PQ tells it can't change the type to duration.
For demonstration I extracted some columns/rows into a sample workbook, [case 2:] created a query inside the sample file extracting the data in the same way - and that did the job...?:lie:
Further examination lead to the finding, that my first attempt (case 1) was addressing the source data in a separate file (located on a network folder) and accessed the file using the Excel.Workbook-function. Here the columns in question got imported in datetime-format.
The second way (case 2) accessed the table using Excel.CurrentWorkbook-function (because query inside the same file...). This was able to import the columns as duration-columns in PQ.
But, to make it even worse, changing the import-format inside PQ of these columns to number-type (to hence after modify to duration) resulted in case 1 all values being added +1, doing the same in case 2 had their right values. So the next step in PQ: transformation from number into duration resulted in case 1 that all times had an extra 24 hours, in case 2 the times were right.
Any explanation, why these 'seemingly similar' functions do their jobs in such different ways? And, more interesting to me: how can I avoid such behavior, how to import duration-values from an excel sheet in an external file into PQ without all the hassle (of course, it is not a big deal to subtract 1 from the imported columns. But that needs extra columns to create, delete original columns, .... and: THIS IS NOT ACCORDING MY SENSE OF BEAUTIFUL CODE... - SCNR). I would prefer to import time-columns (aka duration-columns) from Excel-sheets into PQ, regardless whether done via Workbook- or CurrentWorkbook-function, at least in the same way, preferably preserving their duration-character throughout the import. Any explanation, clue, whatsoever?
Thanks in advance,
Rainer
The attached file "Test.auf.PQ-Import...." is the source data file, containing the query mentioned in case 2. The other file "Zugriff.auf.Test....." is the file trying to access the data in the first file. Here you can enter your path (and maybe filename, if changed) on your system, when trying to test the issue.
The first file contains a table "Zeitenliste" with the mentioned columns and is accessed in both queries. The problem can be seen in the column "Pausenzeit.ersterProzess" in the second file (all times are the original times + 24 hours). The other columns here are without values due to the errors inside the query.
just recently stumbled into problems with data import from an Excel-sheet into PowerQuery (PQ).
The excel sheet carries several columns, some containing durations calculated within the sheet (formatted more or less "[h]:mm:ss"). Now I want to import some of the columns into PQ to do some calculations. [case 1:] But PQ won't import the duration columns in 'the right way', they are imported as datetime columns. Changing to duration doesn't work, PQ tells it can't change the type to duration.
For demonstration I extracted some columns/rows into a sample workbook, [case 2:] created a query inside the sample file extracting the data in the same way - and that did the job...?:lie:
Further examination lead to the finding, that my first attempt (case 1) was addressing the source data in a separate file (located on a network folder) and accessed the file using the Excel.Workbook-function. Here the columns in question got imported in datetime-format.
The second way (case 2) accessed the table using Excel.CurrentWorkbook-function (because query inside the same file...). This was able to import the columns as duration-columns in PQ.
But, to make it even worse, changing the import-format inside PQ of these columns to number-type (to hence after modify to duration) resulted in case 1 all values being added +1, doing the same in case 2 had their right values. So the next step in PQ: transformation from number into duration resulted in case 1 that all times had an extra 24 hours, in case 2 the times were right.
Any explanation, why these 'seemingly similar' functions do their jobs in such different ways? And, more interesting to me: how can I avoid such behavior, how to import duration-values from an excel sheet in an external file into PQ without all the hassle (of course, it is not a big deal to subtract 1 from the imported columns. But that needs extra columns to create, delete original columns, .... and: THIS IS NOT ACCORDING MY SENSE OF BEAUTIFUL CODE... - SCNR). I would prefer to import time-columns (aka duration-columns) from Excel-sheets into PQ, regardless whether done via Workbook- or CurrentWorkbook-function, at least in the same way, preferably preserving their duration-character throughout the import. Any explanation, clue, whatsoever?
Thanks in advance,
Rainer
The attached file "Test.auf.PQ-Import...." is the source data file, containing the query mentioned in case 2. The other file "Zugriff.auf.Test....." is the file trying to access the data in the first file. Here you can enter your path (and maybe filename, if changed) on your system, when trying to test the issue.
The first file contains a table "Zeitenliste" with the mentioned columns and is accessed in both queries. The problem can be seen in the column "Pausenzeit.ersterProzess" in the second file (all times are the original times + 24 hours). The other columns here are without values due to the errors inside the query.
