question

CM-8101 avatar image
0 Votes"
CM-8101 asked ·

Changing data source from one folder to another.

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



power-query-desktoppower-query-m
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MattAllington-5456 avatar image
0 Votes"
MattAllington-5456 answered ·

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"]

· 3 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Matt!

Great, that works just as I needed. Didn't realise I could cut that part of the code out.

Regarding the 50 queries, there are surely many better ways. However, the tool being developed here requires over 20 workstreams / sub-departments to upload their spreadsheets into one input folder, and for Power BI to then merge and model these inputs.

There are roughly 45 different Excel spreadsheets involved, worked on by different people. So as there are so many people involved, we found it easier for them to continue working as they always have done, and for Power BI to do all the data treatment, merging and modelling.

But as you say, there or surely other ways around this!!

Thanks again for your help.

Kind regards,

Chris

0 Votes 0 ·

Glad it worked. Have a look at this other article here https://exceleratorbi.com.au/understanding-power-query-combine/

1 Vote 1 ·

Hello Matt,

I came across to this question and your answer while I was looking a solution to my similar problem.
I have 30+ csv files on my local computer where I create the end excel file with connections to all. Now I moved it to our server and I need to update the folder name from C://.... to Z://... etc.
You mentioned an easier way to do things with multiple files and I would like to know if it will work for my case. Do you mind giving a little more details on what you are referring to?
Thank you.


Ersin

0 Votes 0 ·
MattAllington-5456 avatar image
0 Votes"
MattAllington-5456 answered ·

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.

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

McGimpseyDon-1527 avatar image
0 Votes"
McGimpseyDon-1527 answered ·

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


·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.