Forum Discussion

CalebF22's avatar
CalebF22
Copper Contributor
Oct 01, 2024

Excel Data Model File Path Change

My boss created an Excel data model before I joined the company. He created the data model and queried the data from files within his personal drive. Now that I am here, he wants me to have access to...
BI & Data Analysis
excel
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Oct 01, 2024

CalebF22 

That's to update manually. For future updates use paths as parameters, at least folder part of it.

  • CalebF22's avatar
    CalebF22
    Copper Contributor
    Oct 01, 2024
    Could you elaborate further on the future updates?
    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Oct 01, 2024

      CalebF22 

      If you have queries like

      let
    Source = Excel.Workbook(File.Contents("C:\Test\testA.xlsx"), null, true),
    Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data]
in
    Sheet

      you may create parameter

      and change queries to

      let
    Source = Excel.Workbook(File.Contents( pFilePath & "testA.xlsx"), null, true),
    Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data]
in
    Sheet

      Next the only you need is to edit parameter at one place. In general you may use parameters for full filenames; or keep them in grid, call by another query and combine in main queries, etc. Depends on your needs.

      • jtucker's avatar
        jtucker
        Copper Contributor
        Apr 19, 2025

        I'm trying to use this in Power Query for Excel (Not Power BI)

        I created a Parameter for the windows user name as that is the only portion of the data source file path for sharepoint that changes. I am attempting to insert it into but it seems Excel will not allow my to use a Parameter in this fashion.

        I get this error below. Any ideas?

        "Formula.Firewall: Query 'My Query' (step 'Filtered Rows') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination."

Resources