Changing data source from one folder to another.

C M 21 Reputation points
2020-11-24T20:16:53.947+00:00

Hi!

I am having trouble changing the source folder for my data in Power BI. As I am fairly new on Power BI I have never encountered this issue.

Context: I have created a report on Power BI for a client. There are roughly 50 queries to different Excel sheets, all in one folder. The folder is in my local PC, but when I move it to my client's computer and change the source in Power BI, I get an update error.

Problem: I am trying to change the source using "Data source settings" rather than going one by one and updating the "Advanced Editor" (there are 50 queries)... However, this only updates the first line of the query (it only changes the "Source" step in each query).

Is there any way I can update the "Second step" in all the queries without accessing the Advanced Editor?

The line of code for the query after updating "Data source settings" looks something like this: 

let
Source = Folder.Files("C:\Users\...\New Input Folder"),
#"Second step" = Source{[#"Folder Path"="C:\Users\...\Old Input Folder\",Name="File Name.xlsx"]}[Content],

Thanks in advance for your help.

Kind regards,

Chris

Not Monitored
Not Monitored
Tag not monitored by Microsoft.
35,928 questions
0 comments
Accepted answer
  1. Matt Allington 332 Reputation points MVP
    2020-11-29T02:48:52.45+00:00

    One question is “do you need 50 queries”? There is probably a better way.

    The issue is that the second step refers to the path. Assuming you don’t have any sub folders containing a file with the same file name, you can simply delete the portion referring to the path - you don’t need it as long as there is only 1 file with that name in the folder structure. Just leave [Name="File Name.xlsx"]

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Matt Allington 332 Reputation points MVP
    2021-02-15T02:18:54.717+00:00

    The way I do it is to create a single connection to a folder, and then navigate to the files from there using a relative reference (i.e. make sure the file path is not included in the query steps to navigate to the file). But you need to do this in advance rather than "after the fact". So if you already have all your files hard coded with a full path, you will have to fix them one by one. You should be able to do that via the change data source option.

    0 comments
  2. McGimpsey, Don 1 Reputation point
    2021-03-04T17:37:01.13+00:00

    I have a series of folders that are under a main folder, for instance: \f1\f2 ...\f1\f3 ... and so on. I want to be able to just simply change the drive letter and then I can migrate my code between computer with different drive mappings.

    Can I assign some sort of macro variable or such, to parameterize this drive and just change it in one place?

    Thanks

    Don

    0 comments