none
Add a datestamp to Power Query API data pull RRS feed

  • Question

  • I am using Power Query to pull data through my vendors API.  I would like to date stamp these records which will be added to an Access database.  I know how to create a custom column but I could not find a formula or function to populate the current date when I perform this update.
    Saturday, February 14, 2015 1:23 AM

Answers

  • You can use one of the DateTime functions:

    DateTime.LocalNow

    DateTime.FixedLocalNow

    DateTimeZone.FixedLocalNow

    DateTimeZone.FixedUtcNow

    The challenge however is that each time you will perform a refresh, the entire dataset (old and new records) will contain the latest timestamp in your custom column, and old timestamps will get lost. If this is not what you are looking for, you may need to design a logic where each bulk of data import will be done on a different query and saved to a different workbook with the timestamp in the custom column. Then you will perform an Append on all the workbooks.

    To append all workbooks you can create a function query that loads a worksheet, and then use the "From Folder" import and create a custom column that calls your function, and expands it.

    This is the function query you can use to import each workbook (In this example every workbook you have with the timestamp is in Sheet1).

    let
        getWorkbook = (path as text) =>
    let
        Source = Excel.Workbook(File.Contents(path)),
        Table1_Table = Source{[Item="Sheet1",Kind="Sheet"]}[Data]
    in
        Table1_Table
    in
        getWorkbook

    If you share the format of the table, I can provide you with further instructions.

    Hope it helps,

    Gil 




    Saturday, February 14, 2015 10:02 AM

All replies

  • You can use one of the DateTime functions:

    DateTime.LocalNow

    DateTime.FixedLocalNow

    DateTimeZone.FixedLocalNow

    DateTimeZone.FixedUtcNow

    The challenge however is that each time you will perform a refresh, the entire dataset (old and new records) will contain the latest timestamp in your custom column, and old timestamps will get lost. If this is not what you are looking for, you may need to design a logic where each bulk of data import will be done on a different query and saved to a different workbook with the timestamp in the custom column. Then you will perform an Append on all the workbooks.

    To append all workbooks you can create a function query that loads a worksheet, and then use the "From Folder" import and create a custom column that calls your function, and expands it.

    This is the function query you can use to import each workbook (In this example every workbook you have with the timestamp is in Sheet1).

    let
        getWorkbook = (path as text) =>
    let
        Source = Excel.Workbook(File.Contents(path)),
        Table1_Table = Source{[Item="Sheet1",Kind="Sheet"]}[Data]
    in
        Table1_Table
    in
        getWorkbook

    If you share the format of the table, I can provide you with further instructions.

    Hope it helps,

    Gil 




    Saturday, February 14, 2015 10:02 AM
  • Thanks Gil.  Very helpful.  I am performing incremental data pulls with Power Query so the data/time stamp returned works as I am, as you described, appending my results to other tables in my database.

    The idea of the function query is great.  I am updating about a dozen tables on daily and weekly schedules and was looking for a more automated approach.  I will read up on the function query as an option.

    Thanks again.

    Rich

    Tuesday, February 17, 2015 11:19 PM