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

CalebF22

Copper Contributor

Oct 01, 2024

Could you elaborate further on the future updates?

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.

Forum Discussion

Excel Data Model File Path Change

Resources