none
How can I create dynamic file references in Power Query? RRS feed

  • Question

  • Hi all,

    I'm new at using PowerQuery, and so far I like it. There's one thing I am struggling with though... Once I have set up my PoweQuery connections, I don't find an easy way to change the file to which the query is connecting. I'm using it for a monthyl recurring process, and every month the source data to query on woudl be different. The same in format/structure, but just a different dataset.

    Is there a way to make the source setup more dynamic? Can I for example in a parameters sheet enter the name and path of the new source file and update the queries?

    Currently the Advanced editor shows me following file reference:

    let
        Source = Excel.Workbook(File.Contents("Z:\Templates\EMEA\Source Data Tables\EMEA_EW_Source_Data_for_Power_Queries v1.xlsm")),

    Thanks in advance for suggestions

    Tuesday, June 3, 2014 9:49 AM

Answers

All replies

  • Yes, this is something that you can do with Power Query. Here's how you can do it:

    • Create a table in Excel containing your parameter value. Let's say that it has one column, called ParameterValue, and one row.
    • Create a new Power Query query that gets the data from this table. Call the query something like ParameterQuery.
    • In your original query you will now be able to reference values from your parameter query by saying something like this:

    Source = Excel.Workbook(File.Contents(ParameterQuery[ParameterValue]{0})),

    HTH,

    Chris


    Check out my MS BI blog I also do SSAS, PowerPivot, MDX and DAX consultancy and run public SQL Server and BI training courses in the UK

    Tuesday, June 3, 2014 11:39 AM
  • Thanks for the quick response, Chris, this is exactly what I needed. It works perfectly.

    Regards,

    Niels

    Tuesday, June 3, 2014 6:58 PM
  • Thanks for this information. Would it be possible to store these values in a JSON or YAML file?

    Furthermore, in my situation I am syncing files between a work computer and a personal computer so the root filepath is different (based on my username). Is it possible to just use a variable in PowerQuery that can pull my UserName from the PC and insert that into the source line of the query?

    • Edited by Jay Killeen Friday, August 21, 2015 6:55 AM
    Friday, August 21, 2015 6:30 AM
  • I did using this post as reference.

    I created the table as above and then:

    - if you don't want to create macros, update the reference cell manually

    - otherwise create a startup macro to update it based on your environment variable (you can google for examples how)

    After that you just use that value in the string to your path, like:

    let
        CurrUser = Record.Field(Excel.CurrentWorkbook(){[Name="CurrUser"]}[Content]{0},"CurrUser"),
        Source = Csv.Document(File.Contents("C:\Users\" & CurrUser & "\Downloads\data.csv"),[Delimiter=",", Encoding=65001]), ...  ... ...

    Hope it helps.


    Wednesday, January 18, 2017 3:48 PM
  • Hi,

    When I am trying to do the above logic after reading it from the excel file and having the parameter value as the full path with file name including double quotes I am getting illegal character path error and if I am not passing double quotes it is unable to read the file name.

    Could anyone please help me in reading the filepath and filename dynamically in power BI


    shobhit

    • Proposed as answer by JHJ VIA Saturday, October 31, 2020 12:01 PM
    • Unproposed as answer by JHJ VIA Saturday, October 31, 2020 12:01 PM
    Sunday, September 27, 2020 8:12 PM
  • Hi

    Had the same problem, found the answer in a video posted by Celia Alves - https://youtu.be/vZuMCEkL2eM
    Look at 11:29
    No double quotes, just change the privacy settings.

    File > Options and settings > Query Options
    In "Privacy" choose "Ignore the Privacy Levels..."
    Saturday, October 31, 2020 12:10 PM