Forum Discussion

FreemanZ's avatar
FreemanZ
Icon for Super User rankSuper User
3 years ago
Solved

How to bulk change the source location for queries from different files in the same folder?

How to change the source location for queries from different files in the same folder?
 
Supposing I have 10 Excel files of different dimentions in one folder. Someday, I rename the folder or I move the whole foler elsewhere. I can update the folder in the first Source step, for one query after another. Is there a good way to change the source easily? 
 
If all queries are from one excel file, it could be done with parameters. 
If all queries are from different excel files but with similar type and to be appended, it is also easy. 
 
Is there similar convinent way for queries from different files of different type/dimention in the same folder?
Need Help
  • FreemanZ's avatar
    FreemanZ
    3 years ago

    Thank you Jorge. I found this article works and solves the problem.

    https://powerbi.tips/2016/08/using-variables-for-file-locations/

     

    Adding the back slash at the end of the copied file path saves my day. Preciely the line below:

    = Excel.Workbook(  File.Contents(   Folder  &   "2000 Medals.xlsx") ,   null , true )

    Thank you Jorge to give me the hint and confidence working in this direction. 

6 Replies

  • JorgePinho's avatar
    JorgePinho
    Icon for Solution Sage rankSolution Sage
    3 years ago

    The same way you can use parameters for one excel file you can also make for the folder path.

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

    hi Jorge, 

    Many thanks for the quick reply.

    Could you stipulate how it works? Or help google an relevant article or video?

     

    i tried and failed. 

    I get below:

    at point 1, the parameter is used. at point 2, it is still with the raw folder path. 

    If i replace the folder path with Source, I get an error. 

    • JorgePinho's avatar
      JorgePinho
      Icon for Solution Sage rankSolution Sage
      3 years ago

      I reccomend you the following:

       

      1. Create a parameter with the folder path

      2. Go to New Source choose Folder

      3. When it asks for the path change to parameter 

       

      • PD_D's avatar
        PD_D
        Regular Visitor
        1 year ago

        Thanks a lot for sharing this trick, works well and saved a lot of time.. 

    • FreemanZ's avatar
      FreemanZ
      Icon for Super User rankSuper User
      3 years ago

      Thank you Jorge. I found this article works and solves the problem.

      https://powerbi.tips/2016/08/using-variables-for-file-locations/

       

      Adding the back slash at the end of the copied file path saves my day. Preciely the line below:

      = Excel.Workbook(  File.Contents(   Folder  &   "2000 Medals.xlsx") ,   null , true )

      Thank you Jorge to give me the hint and confidence working in this direction.