differences between Excel.workbook... and ~.CurrentWorkbook function

Rainer

New member
Joined
Apr 15, 2021
Messages
2
Reaction score
0
Points
0
Excel Version(s)
365
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.
 

Attachments

  • Test.auf.PQ-Import.von.Zeiten.xlsx
    48.4 KB · Views: 10
  • Zugriff.auf.Test.auf.PQ-Import.xlsx
    30.2 KB · Views: 8
It seems Power Query has a little problem. If an hour format is imported from an external workbook, it is displayed as date/time. That would still work. But all times smaller than 24 hours are displayed with the date 1899.12.31, and as you have already noticed, 24 hours are then added when the format is changed to "duration".

If you google the problem, you will find that this problem has existed for a long time and that there are only workarounds.

If you import the query from the workbook, the times are displayed as decimal numbers that can be changed to the format "duration" without any problems.

Enclosed is my workaround for the import from another workbook.

Here the step I take for the duration calculation:

Code:
Calculate_Duration = Table.AddColumn(#"Removed Columns", "Duration", each if Date.From([Hours_1]) < #date(1900,01,01) 
then Duration.FromText(Text.End(Text.From([Hours_1]),8)) else Duration.From(Number.RoundDown(Number.From([Hours_1]) / 1, 0) - 1) + Duration.FromText(Text.End(Text.From([Hours_1]),8)))
 

Attachments

  • Duration Test.xlsx
    19.6 KB · Views: 3
  • Import from workbook Duration Test.xlsx
    17.9 KB · Views: 9
I have now adapted the workaround "Calculation of duration" so that it can be used directly for the import from a workbook as well as for the import from a worksheet.

In the current sample folder, the first query imports from itself via "Excel.Workbook" and the second query imports directly from the spreadsheet via "Excel.CurrentWorkbook".

In both queries, the column "Time" is used as the source for the column "Duration".

The parameter value for the import from the workbook is determined by formula.
 

Attachments

  • PQ - Duration Test - Import from Workbook vs. Worksheet.xlsx
    25.6 KB · Views: 4
Back
Top