Forum Discussion
Changing a power query source
This seems very basic, but haven’t figured it out. I have an existing Power Query based on loading a table from a single Excel file. I want to replace the source with a folder containing multiple Excel files that should be combined.
How can this be done? Or am I supposed to delete the whole query and its table from the data model, and then recreate?
Yes, it's better to recreate everything from scratch. From File and From Folder are two different connectors. You may re-use your current query to modify Transform File query in From Folder generated set.
Perhaps this can help you here.
Note: The Power Query function and the one in the Excel files
The functions and displays used only work correctly if you are using at least the Excel version Excel 2016.
Ribbon data
In the command panel, click Get Data and Transform
on the Get Data command.
Select the Start Power Query Editor... entry here.
In the Power Query Editor ribbon, enable the
command sequence.
Home tab -> Data Sources command group -> Data Source Settings.
The Data Source Settings dialog box opens.
Select the data source to customize and click the Change Source button.
Adjust the file path by clicking the button
Browse... click.
Then select the appropriate file path on your computer.
Close all open dialog boxes by clicking on OK and
Close.
Exit the Power Query Editor by typing the command sequence
File tab -> Run the Close and Load command.
The import of the data can take a few seconds.
Please be patient.
Then save the Excel file with the Power Query function. The data should then be updated in the corresponding spreadsheets.
Via the DATA button set up in the Excel file
UPDATE these are imported from this import directory.
I know I don't know anything (Socrates)
- Thanks, but the issue is that the current file path is a single Excel file. Changing the data source doesn't seem to allow me to select a folder and specify to combine multiple files.
Combine files (binaries) in Power BI Desktop
Combine Multiple Excel Workbooks in Power Query
How to Combine Tables with Power Query
According to the search results, there are several steps you need to follow1234.
A brief summary is:
Create a query from a single Excel file and convert it to a function
Create a query from a folder and select the Combine Files option
Apply the function to each file in the folder and combine all the data
You can view the search results for more details
For more precise information, I am unfortunately also overwhelmed, as far as pq is concerned, my knowledge is limited. Don't know if the problem is with PQ or with Excel itself.
If all this doesn't help, maybe starting in safe mode and trying the changeover there would help to see if it's Excel, but none of this grant.
Thank you for your understanding and patience
I know I don't know anything (Socrates)
