none
Query sourse depending on the file name

    Question

  • The source of my query is a text file which has a date in the file name in the format YYMMDD (for example PROD_00000702_20130821.utp). I want the text file which has a today's date in the file name to be used as a source file when I open the Excel and the data are refreshed. The source files are stored in the same folder.

    What should the source function or file path contain to upload the file with the today's date in the file name?

    Thanks.

    Wednesday, August 21, 2013 1:07 PM

Answers

  • Hi Susla,

    You can do this by modifying the generated formulas in your query. To do that, you can first enable "Advanced Query Editing" in the Options dialog. Then, back into your query you will see an script icon at the right end of the fx bar. This will give you access to the formulas for all steps within the current query.

    You will have a Source step that has the full path to your file, as a static Text value (i.e. "PROD_00000702_130821.utp"). In order to make this dynamic, and based on the current date, you will need to combine the use of a few PQ Formula Language and Library functions. You can find more information about these and other functions in our Online Help contents, including access to the full language/library specifications (link).

    • DateTime.LocalNow(): This gives you the current date/time based on your time zone.
    • Date.From(datetime): This turns a date/time into a date value.
    • Date.ToText(date, format): This turns the specified date into a Text value following the format specified, such as "YYYYMMDD".
    • Text.RemoveRange(text, offset, count): Note that the "YYMMDD" format is not directly supported by Date.ToText, so you need to drop the first two characters in the Text value. You can use Text.RemoveRange for that.
    • Text.Combine({list of Text values}, separator): Finally, you'll need to combine the static and dynamic pieces of your file name into a single Text value, which you can then use to replace the fully static path that you currently have.

    Combining these functions and using the sample file name that you mentioned as an example, it comes down to something like this:

    Text.Combine({"PROD_00000702_", Text.RemoveRange(Date.ToText(Date.From(DateTime.LocalNow()), "YYYYMMDD"), 0, 2), ".utp"}, null)

    You should be able to replace the "path" in your query Source step (i.e. File.Contents("path")) with this formula.

    Hope this helps. Thanks.
    M.



    Wednesday, August 21, 2013 6:20 PM

All replies

  • Hi Susla,

    You can do this by modifying the generated formulas in your query. To do that, you can first enable "Advanced Query Editing" in the Options dialog. Then, back into your query you will see an script icon at the right end of the fx bar. This will give you access to the formulas for all steps within the current query.

    You will have a Source step that has the full path to your file, as a static Text value (i.e. "PROD_00000702_130821.utp"). In order to make this dynamic, and based on the current date, you will need to combine the use of a few PQ Formula Language and Library functions. You can find more information about these and other functions in our Online Help contents, including access to the full language/library specifications (link).

    • DateTime.LocalNow(): This gives you the current date/time based on your time zone.
    • Date.From(datetime): This turns a date/time into a date value.
    • Date.ToText(date, format): This turns the specified date into a Text value following the format specified, such as "YYYYMMDD".
    • Text.RemoveRange(text, offset, count): Note that the "YYMMDD" format is not directly supported by Date.ToText, so you need to drop the first two characters in the Text value. You can use Text.RemoveRange for that.
    • Text.Combine({list of Text values}, separator): Finally, you'll need to combine the static and dynamic pieces of your file name into a single Text value, which you can then use to replace the fully static path that you currently have.

    Combining these functions and using the sample file name that you mentioned as an example, it comes down to something like this:

    Text.Combine({"PROD_00000702_", Text.RemoveRange(Date.ToText(Date.From(DateTime.LocalNow()), "YYYYMMDD"), 0, 2), ".utp"}, null)

    You should be able to replace the "path" in your query Source step (i.e. File.Contents("path")) with this formula.

    Hope this helps. Thanks.
    M.



    Wednesday, August 21, 2013 6:20 PM
  • Great, thank you.

    Could you also advise how should the above query be changed to upload file from previous day (file with yesterday’s date in file name).

    Thank you.

    Monday, August 26, 2013 2:27 PM
  • Sure.

    It would be only a minor tweak in the first step: instead of directly using DateTime.LocalNow(), you should subtract one day from this date by using a duration (composed of "days, hours, minutes, seconds") value: DateTime.LocalNow() - #duration(1, 0, 0, 0)

    Thanks,
    M.

    Monday, August 26, 2013 2:59 PM
  • Thank you.

    And how should be the query changed if I want to upload the file from previous day (file with yesterday’s date in the file name) if it was a workday but if the previous day wasn’t  a workday (Sunday) the file from Friday should be uploaded. In Excel sheet this would be written probably like this: =TODAY()-IF(WEEKDAY(TODAY(),2)=1,3,1)

    Wednesday, August 28, 2013 4:04 PM
  • Hi Susla,

    Unfortunately we don't have an equivalent to WEEKDAY in Power Query yet.

    You can still replicate the logic of this function "inline", or even create the function as a separate query, with something like this:

    IsWeekDay = (d) =>

    let

       dayofweek = Date.DayOfWeek(d)

    in

         not (Day.Saturday = dayofweek or Day.Sunday = dayofweek)

    Thanks,
    M.

    • Proposed as answer by Bda75 Tuesday, January 21, 2014 8:39 AM
    • Unproposed as answer by Bda75 Tuesday, January 21, 2014 8:39 AM
    Wednesday, September 04, 2013 7:33 PM