add sharepoint csv files to query

[Anonymous]

I have created a Power BI report based on an annual CSV file in SharePoint.  Every month a new file with the same format will be placed on the same SharePoint location.  What is the best and most automatic way to make sure these files are included?  

I tried to edit the existing query and use selection criteria to select only csv files that have the common terms in the filename.  Ex:  MonthYear XXX report. csv  

When I did enter the selection criteria, the file list looked correct, having two files 2019 XXX report.csv and Jan2020 XXX report.csv

However, when I click combine, nothing happened.

Then I tried creating a new query to the SharePoint site.  All files were shown.  I then selected Continue, Combine and Load.  Only the annual file seemed to be included.  

What should I do?  I'd like to set this up so that the report will automatically draw from the files on SharePoint once refreshed.  Is this possible?

[trebgatte · Accepted solution]

Take a look at my blog post here, as this technique will work with CSV files as well. https://marqueeinsights.com/how-to-merge-multiple-excel-files-with-power-bi/

Hope this helps.

--Treb, Power BI MVP

https://getstartedwithpowerbi.com 

[Anonymous]

Doubling issue solved!  Turns out csv file had a bunch of blank rows.  Still wondering if I can copy the query language using the advanced editor to an existing pbix report.  Please advise.

[trebgatte]

Yes, you should be able to copy it over using the Advanced Editor, assuming the dataset names and data sources are the same. You may have to do a bit of cleanup.

I'm glad it worked! Check out our resource page at https://getstartedwithpowerbi.com for other SharePoint related articles.

--Treb, Power BI MVP

[Anonymous]

This worked to load multiple CSV files from SharePoint!  Thanks for the very clear instructions.  I tested adding one more file to the SharePoint folder with nomenclature that meets the selection criteria.  I then did a refresh.  The new test record is included, but it doubled the records instead of just adding one as expected.  Any idea what caused this?

Also, I did all this in a new pbix.  Can I copy the query language to my pbix with all the visualizations using the advanced query editor?