locked
Querying a table containing datetime column. RRS feed

  • Question

  • Hello,

    A Power Query newbie here.

    I have an Excel table formed by 5 columns:

    Time Open High Low Close
    10/7/2016 11:00:00 PM 1.11958 1.12004 1.11935 1.11995
    10/7/2016 10:00:00 PM 1.11801 1.11979 1.11797 1.1196
    10/7/2016 9:00:00 PM 1.11712 1.11802 1.11699 1.11798
    10/7/2016 8:00:00 PM 1.11671 1.11739 1.11633 1.11713
    10/7/2016 7:00:00 PM 1.11551 1.11682 1.11465 1.1167
    10/7/2016 6:00:00 PM 1.11752 1.11798 1.11494 1.11551
    10/7/2016 5:00:00 PM 1.119 1.11987 1.11737 1.11751
    10/7/2016 4:00:00 PM 1.11708 1.12047 1.11649 1.119
    10/7/2016 3:00:00 PM 1.11416 1.11783 1.11163 1.1171
    10/7/2016 2:00:00 PM 1.11403 1.11441 1.11353 1.11415
    10/7/2016 1:00:00 PM 1.1124 1.11424 1.11209 1.11399
    10/7/2016 12:00:00 PM 1.11281 1.11298 1.11195 1.11241
    10/7/2016 11:00:00 AM 1.11123 1.11314 1.11089 1.11282
    10/7/2016 10:00:00 AM 1.11139 1.11146 1.11038 1.11125
    10/7/2016 9:00:00 AM 1.11201 1.11246 1.1108 1.1114
    10/7/2016 8:00:00 AM 1.11167 1.11199 1.11131 1.11199
    10/7/2016 7:00:00 AM 1.11121 1.11173 1.11117 1.11167
    10/7/2016 6:00:00 AM 1.11249 1.11255 1.11098 1.11121
    10/7/2016 5:00:00 AM 1.11301 1.11351 1.11247 1.11249
    10/7/2016 4:00:00 AM 1.11313 1.11352 1.11171 1.11301
    10/7/2016 3:00:00 AM 1.11411 1.11459 1.11303 1.11315
    10/7/2016 2:00:00 AM 1.11453 1.11513 1.11246 1.11412
    10/7/2016 1:00:00 AM 1.11466 1.11485 1.11419 1.1145
    10/7/2016 12:00:00 AM 1.1148 1.11516 1.11407 1.11465
    10/6/2016 11:00:00 PM 1.11493 1.11545 1.11461 1.11489
    10/6/2016 10:00:00 PM 1.11401 1.11496 1.11395 1.11492
    10/6/2016 9:00:00 PM 1.11554 1.11557 1.11399 1.114
    10/6/2016 8:00:00 PM 1.11606 1.11636 1.11551 1.11551
    10/6/2016 7:00:00 PM 1.11565 1.1165 1.11529 1.11606
    10/6/2016 6:00:00 PM 1.1169 1.11734 1.11463 1.11564
    10/6/2016 5:00:00 PM 1.11785 1.11859 1.11621 1.11689
    10/6/2016 4:00:00 PM 1.11794 1.11843 1.11699 1.11786

    This is a hourly table with four values (numeric fields) per hour:

    Open: First value in the hourly period

    High: Highest value in the hourly period

    Low: Lowest value in the hourly period

    Close: Last value in the hourly period

    From this table I need calculating a daily table with the same structure, with 1 row per active day (if a day has no activity in the hourly table, it shouldn't appear in the daily table)

    Each daily row should show:

    Open: First value in the daily period

    High: Highest value in the daily period

    Low: Lowest value in the daily period

    Close: Last value in the daily period

    Please can you give me a hint?

    Best regards

    Francisco


    • Edited by frangonve Saturday, October 8, 2016 9:14 PM
    Saturday, October 8, 2016 8:09 PM

Answers

  • let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        ChangedType = Table.TransformColumnTypes(
                          Source,
                          {
                            {"Time", type datetime}, 
                            {"Open", type number}, 
                            {"High", type number}, 
                            {"Low", type number}, 
                            {"Close", type number}
                          }
                      ),
        SortedDateTimeDesc = Table.Sort(ChangedType,{{"Time", Order.Descending}}),
        DuplicatedTimeColumn = Table.DuplicateColumn(SortedDateTimeDesc, "Time", "Date"),
        ChangedDateTime = Table.TransformColumnTypes(DuplicatedTimeColumn,{{"Date", type date}}),
        ReorderedColumns = Table.ReorderColumns(ChangedDateTime,{"Date", "Time", "Open", "High", "Low", "Close"}),
        GroupedRows = Table.Group(
                          ReorderedColumns, 
                          {"Date"},
                          {
                            {"Day Open", each List.Last([Open]), type number}, 
                            {"Day High", each List.Max([High]), type number}, 
                            {"Day Low", each List.Min([Low]), type number}, 
                            {"Day Close", each List.First([Close]), type number}
                          }
                       )
    in
        GroupedRows

    • Marked as answer by frangonve Saturday, October 8, 2016 10:07 PM
    Saturday, October 8, 2016 9:44 PM

All replies

  • let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        ChangedType = Table.TransformColumnTypes(
                          Source,
                          {
                            {"Time", type datetime}, 
                            {"Open", type number}, 
                            {"High", type number}, 
                            {"Low", type number}, 
                            {"Close", type number}
                          }
                      ),
        SortedDateTimeDesc = Table.Sort(ChangedType,{{"Time", Order.Descending}}),
        DuplicatedTimeColumn = Table.DuplicateColumn(SortedDateTimeDesc, "Time", "Date"),
        ChangedDateTime = Table.TransformColumnTypes(DuplicatedTimeColumn,{{"Date", type date}}),
        ReorderedColumns = Table.ReorderColumns(ChangedDateTime,{"Date", "Time", "Open", "High", "Low", "Close"}),
        GroupedRows = Table.Group(
                          ReorderedColumns, 
                          {"Date"},
                          {
                            {"Day Open", each List.Last([Open]), type number}, 
                            {"Day High", each List.Max([High]), type number}, 
                            {"Day Low", each List.Min([Low]), type number}, 
                            {"Day Close", each List.First([Close]), type number}
                          }
                       )
    in
        GroupedRows

    • Marked as answer by frangonve Saturday, October 8, 2016 10:07 PM
    Saturday, October 8, 2016 9:44 PM
  • Thanks a lot.

    Where can I find reference information (manuals, tutorials,...) to learn this kind of transformations?

    Francisco

    Saturday, October 8, 2016 10:08 PM
  • Hi Francisco,

    The transformations were generated through the UI. In addition, I removed the spaces in step names in the UI (Rename). I reformatted the generated code, and in the group step, added aggregations for open and close.

    Sunday, October 9, 2016 1:32 PM