Forum Discussion
How to load & combine data from multiple sheets in multiple files in a SharePoint folder
I got some help here in our office and wanted to circle back and update my question here with the answer.
1) I selected SharePoint folder as the data source and entered the link.
2) from the list of files in the site, I selected Transform Data.
3) I expanded the Binary column to see the sample file and the sheets in the file.
4) I selected the first sheet to load from the sample file and pressed OK.
5) I switched to the power query window and changed the query name.
6) in the Applied Steps pane, I deleted everything after the Invoke Custom Function1 step.
6) the last column in the table has a title of Transform File, and I expanded it.
7) on the left hand pane, under Helper Queries, select the Transform Sample File query.
😎 in the applied steps, select the Filtered Rows step and select the sheets to include to merge.
that concludes the steps to select multiple sheets from multiple files in a directory.
9) then, you can select the query that you're building in the left hand pane again and add any other transformations (such as expanding Transform file to select which fields to include).
I got some help here in our office and wanted to circle back and update my question here with the answer.
1) I selected SharePoint folder as the data source and entered the link.
2) from the list of files in the site, I selected Transform Data.
3) I expanded the Binary column to see the sample file and the sheets in the file.
4) I selected the first sheet to load from the sample file and pressed OK.
5) I switched to the power query window and changed the query name.
6) in the Applied Steps pane, I deleted everything after the Invoke Custom Function1 step.
6) the last column in the table has a title of Transform File, and I expanded it.
7) on the left hand pane, under Helper Queries, select the Transform Sample File query.
😎 in the applied steps, select the Filtered Rows step and select the sheets to include to merge.
that concludes the steps to select multiple sheets from multiple files in a directory.
9) then, you can select the query that you're building in the left hand pane again and add any other transformations (such as expanding Transform file to select which fields to include).