cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
UncleLewis
Helper II
Helper II

Combine Files From SharePoint Folder

Hi all,

I am using Power Query in Excel to try to combine files in a folder on my Sharepoint.

Initially, I uploaded csv files, but I kept getting error messages in in PQ when I tried to change the Sample File to the first csv file rather than the first file returned in PQ. So on my desktop I opened each csv file and resaved as .xlsx file. I uploaded the .xlsx file to a SharePoint Folder and deleted the csv files.

 

After many attempts and reboots, Power Query Combine Dialog finally shows all .xlsx files - no .csv files.

 

I selected Transform Sample File in the Queries Pane and add all transformations on my sample file.

 

However, once I clicked Close and Load, I just get

 

The Other Queries Folder has a query that is showing a warning :
PQ_Error.png

The file does need a bit of cleanuip in Power Query which I did in the Transform Sample File
Original:

Orig_File.png

1 ACCEPTED SOLUTION

So before you do the Combine operation, use the filters to filter out everything you do not need.

For example, set the filter to only keep files that end in CSV. If there are stilll files there you don't want, then start filtering to keep only files with certian words in them.

Then do the combine operation. Not only will this select a valid sample file, it will keep the entire thing from breaking. Even if this had selected the file you wanted first, it would have used that file as a template to transform your Timesheet file, which would have likely broken.

 

One other thing you can do that I recommend. If your files are in one folder, use SharePoint.Contents().

When you create a connection to SharePoint folders it uses the SharePoint.Files() function by default. In the formula bar for the SOURCE row, change it to say SharePoint.Contents("https://blahblahblah" [apiwhtever]) - just change Files to Contents. Case sensitive.

Now you will browse by folder. Usually you click on Shared Documents table, then down to the folder you want. From there you can transform an entire folder.

 

This is the best way when all files are in one folder. Much faster than SharePoint.Files performs.

One caveat - if you navigate down to the wrong folder there is no way to go back up without manually editing the M code. Usually faster to just delete the NAVIGATION step and start over from Shared Documents.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

7 REPLIES 7
UncleLewis
Helper II
Helper II

Thanks edhans,


I deleleted the .xlsx files from the SharePoint Folder and copied up the 3 csv files.
I stared a new consolidation workbook and as usual I received the Timesheet.xlsx file and the 3 csv files.

This time, instaed of selecting ther Combine Button, I selected the Transform Data Button

I filtered out the .xlsx file so only the 3 csv files remain.
I then combine the 3 files, so far so good.
step1.png

I then started going through the transformation steps on the Transform Sample File
I see an error on Query1 in Other Queries[1] Folder when I select the 3 columns I want to keep, Right-click, and select Remove Other Columns. I clicked on Query1, I removed ChangedType from Applied Steps. The error went away.

I went back to the Transform Sample File to continue any needed transformations.

The 3 files are now merged as required.
Thanks for your good help!

-w

final.png

In Power BI, there is a setting to not detect data types on unstructured sources (CSV files, Excel files, etc.) I have that enabled, becaause that #"Change Type" that gets automatically added can cause issues. I prefer to change the types only on the files I keep, and I prefer to decide the type.

edhans_0-1624055643427.png

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Thanks edhans,

It appears that is the default setting in Power BI.
In all items above, I'm using Power Query in Excel.

Thanks

-w

It is also in Excel's Power Query, but only if you are on Office 365. It was added in mid-late 2020, so it isn't in Office 2016 or 2019. Maybe Office 2022. But Office 365 today.

edhans_0-1624057918282.png

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
UncleLewis
Helper II
Helper II

Thanks edhans,

 

The first file it is picking is a timesheet file that I do not need - just the 3 csv files in MyDemo Folder.

I tried entering additional URL info when prompted for the SharePoint Folder but I received an error that I could only select the root of the SharePoint Folder.

 

Thanks,

-w

So before you do the Combine operation, use the filters to filter out everything you do not need.

For example, set the filter to only keep files that end in CSV. If there are stilll files there you don't want, then start filtering to keep only files with certian words in them.

Then do the combine operation. Not only will this select a valid sample file, it will keep the entire thing from breaking. Even if this had selected the file you wanted first, it would have used that file as a template to transform your Timesheet file, which would have likely broken.

 

One other thing you can do that I recommend. If your files are in one folder, use SharePoint.Contents().

When you create a connection to SharePoint folders it uses the SharePoint.Files() function by default. In the formula bar for the SOURCE row, change it to say SharePoint.Contents("https://blahblahblah" [apiwhtever]) - just change Files to Contents. Case sensitive.

Now you will browse by folder. Usually you click on Shared Documents table, then down to the folder you want. From there you can transform an entire folder.

 

This is the best way when all files are in one folder. Much faster than SharePoint.Files performs.

One caveat - if you navigate down to the wrong folder there is no way to go back up without manually editing the M code. Usually faster to just delete the NAVIGATION step and start over from Shared Documents.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

edhans
Super User III
Super User III

Why did you change the sample file it picked? The files all need to be the same format - same columns, data, etc. The sample file is just the framework to get going. No need to pick the file you want. If you have 200 files, all the same format, just different data (months, weeks, divisions, whatever) it shouldn't make a difference what the sample file is. It will use 1 file schema to process 200 files.

If the files are different, you need to filter them before doing a combine operation, and then transform each group separately. You cannot have one sample file created automatically that will processes different formats. You could do it with one, but it would involve a lot of manual coding.

I would remove the XLSX files, put the CSV files back and start over. Let Power Query do its thing. Then if you don't like the results and can explain why the sample file it chose was inferior to the one you wanted it to choose, post back with the differences of those two files.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors