Connect Excel / CSV files from SharePoint to Excel or Power BI

[@Scott-lc5kh]

I prefer and advise Sharepoint.Contents. This more navigable in the UI, like a traditional folder structure. Plus it doesn’t query a list of all the files on the Sharepoint site. Once you start getting into large quantities of files on a Sharepoint site, Sharepoint.Files tends to cause issues.

[@cristianprifti]

I also love this method, but it does come with strings attached, if the SharePoint is changed and the root path is not identical anymore.

[@ryanwitte3475]

​@cristianprifti the same is true for SharePoint.Files as well in the folder filter step.

[@Scott-lc5kh]

@@ryanwitte3475 exactly. plus any dev worth a grain of salt should parameterise the SP root anyway.

[@antoniofelez4674]

SharePoint.content gets tricky when you want to combine the binary in the content column. If there is a sub folder it shows as “table” so you can either combine the current folder or go into sub folder and combine that…..

[@antoniofelez4674]

If there is a worker around please share, I’m new to this thing

[@ExcelWithChris]

Love the way you combine without all the helper queries!! In the beginning when I just started with PQ, I struggled to underssstand what all these "automated" queries where.

[@amitpatel75]

Hii , chandeep , You are best teacher , and i can easily understand what content you deliver, Kindly request you to create more content for SharePoint, how it works into Power BI , excel and what are security features of SharePoint..

[@donduck7845]

This has really been needed! I've worked with people doing it so many different ways!

[@rajkamal6866]

Good to see you after 12 years, I was part of your excel camp in noida.

[@fishysenpai]

thanks a lot for this informative guide... there were lots of undetectable error when i chose to combine files automatically, but following the steps that you are mentioning I was easily able to combine the files and remove the errors from a transformation step early on without getting the error cascaded in subsequent steps. it definitely saved a lot of time :)

[@rayyap9289]

This is super helpful! Just tried it and works perfect! Thank you so much!

[@giorgitsomaia2812]

another great video, thanks Goodly! I have one small note though, before combining tables you need to make sure that there are not DefinedNames (this can come from ex. filtering source file), which will result duplicating the data

[@surajpandita]

Great content What if files have multiple headers and we want to promote second row as header row without deleting first header row Please advice

[@GoodlyChandeep]

kzbin.info/www/bejne/nKLPaYBupsmJiNk

[@arbazahmad7177]

Excellent.. thanks for sharing 👍

[@DavorMihaic]

Hi Chandeep. First of all, love your videos! I really like the way you explain things. Keep going! Can you do a video about the right way to connect and manage txt files? I've found some complications with this.

[@sharmarudra]

Hey Chandeep, thanks for helping us with the wonderful tutorial. Can you please let us know how to retain the file name as well? May be a video would be good. Thank you.

[@skv4611]

Great content. Can you tell me is there any performance difference between Table.Combine Vs Table.Expand?🤔 coz many times I see your videos, preferred with Combine. Please suggest

[@josealvesferreira1683]

Very good, congratulations

[@shaileshsingh8906]

Thanks Chandeep, looking for this content

[@emanueledelzoppo5164]

Nice video as always! What about a way to preserve the name of the file as new column?

[@dthibby]

Thanks so much, was very helpful!!!

[@nelson_k_d]

Hi Chandeep, Thanks a lot for your videos, have learnt a lot from you. We can obtain files from OneDrive online too into Power Query using the UI & having been doing it since a long time (hope I did not misunderstand you).

[@dilipinamdar5523]

Excellent.. Thanks🙏

[@stevedavies5588]

Great video. Thanks

[@desmondlee3434]

Hi Chandeep, is there a way to have dynamic sharepoint folder path?

[@KuldeepSingh-nq1vi]

Very Nice Video..

[@Dev_Bartwal]

Super 👌

[@ChamroeunSiv-om5qq]

after connecting the excel to the sharepoint, you have to then import that excel to PowerBi right?

[@armiiarmy]

How or Dose it can refresh power query automatically or by scheduled refresh in the excel?

[@shubhabratadey]

Does these processes address inclusion or exclusion of columns automatically in the files? Also, the file names/ source names are missing after the final step. How to address this?

[@IBRAOMER1]

Hey Chandeep, I've been trying to retrieve the folder path for a folder in my OneDrive Personal account. I need it for Excel Power Query to append files from that folder. Despite Googling and watching KZbin tutorials, I'm still struggling to find the folder path. Any suggestions?

[@mayyyur_patil]

Struggling with the same problem!!

[@kdk9885]

Dear Mr Goodly, First of all, thank you very much for the video. It helped me tremendously to create an overview of various Excel files. Everything actually works fine apart from 1 thing not. Could you perhaps help me further? I have designed a form in an app that automatically fills in an Excel file with the same structure every time, only including the information that is important to me. When this Excel file is sent to my e-mail address, I automatically save it to a SharePoint folder via Power Automate. I have imported this SharePoint folder, as described in your video, into Excel. However, the problem is that Excel cannot read new files automatically and gives a [dataFormat.Error]. The data can only be refreshed if I briefly open the new file on SharePoint. If I don't do this, Excel does not recognise the file as an Excel file. Do you perhaps have a tip or idea to solve this problem? To explain the situation a little further: several files are replaced with a new version every day. These Excel files keep a record of all open points of an installation. I hope you can help me as I have been struggling with this for several weeks. Thanks in advance for your help.

[@SandeepYadav-vm5hd]

Please create video on embedded SharePoint

[@marcusisaksson]

Love this solution! However, I'm trying to troubleshoot why I end up with three (!) different results depending on whether I (1) use this method to combine XLS files, (2) load data from a folder, or (3) just open a file directly from the folder. These are XLS files, not XLSX. Everything works fine when I open a single file in Excel, but the other methods completely mess with the columns, and the solution above even excludes one (!) single cell from a sheet. I can’t really wrap my head around how just one cell could be excluded. Could it be that the language settings are being applied differently in each case? I’d be incredibly grateful if someone could point me in the right direction! :-)

[@muhannadbasalem580]

how to make it to automatically refresh on a daily basis from my shared files?

[@jorstube]

It Works when someone share a folder with one drive?

[@datouwangmei]

Is to create a new team to upload files in teams

[@Simon-vc1wk]

I got my solution to work by grabbing the .csv I require from sharepoint. The csv contains 8k rows but I find power query evaluates for some time

[@SamehRSameh]

In last one if i want to combine 2 column not one , how ???

[@snchitre]

Hi! How to get data from SharePoint where one has read access only.

[@datouwangmei]

There is no folder in my sharepoint website, is there any setting I need to do

[@datouwangmei]

I can't find share with us,Do I need to set up a shared folder, or is this file shared with me by someone else

[@datouwangmei]

Fixed, is to create a new team to upload files

[@laionegan]

What is the underscore _?

[@cristianprifti]

It is the technical way of saying, each (row)

[@sibidi894]

His eyes be like: "open this video will you!". 😂

[@GoodlyChandeep]

I am glad you opened. 😃

[@suryamohan5495]

Hey Bro, I have seen that power query can be refreshed from Web as well, can you do a video about it?

[@vansree]

Using this technique, I created the PBI dataflow and able to code to get CSV files from SHAREPOINT. The csv files are like 2 MB to 10MB. Overall, all files size is around 2.2GB+. Each csv file is like countries in a region, and for each country, order summary and detail rows. Power Query transformations are very simple and max of 20 lines in the combine file transformation. (header, fill down for detail rows, cleanup, data type ... nothing big there) Since I have around 2GB+ CSV files on SharePoint and pretty slow while combining. Performance or improvement question: Is there a better approach or solution when you have such large data? TIA

[@cristianprifti]

Try what Scott mentioned, of changing from .Files to .Contents connector. I tried it with a large SharePoint and it works faster. If you also need to enhance the data, you can also use 2 separate dataflows, one that just loads the data raw and one that will apply certain rules.

[@vansree]

@@cristianprifti I tied that method it is even slow. I e., splitting two flows. First flow fetches files and add index for line number. Second flow parse, transform, split and model the data as tabular

[@ttilta]

@@vansree I have a similar predicament and I intend to try something at the end of the month, if you have time you can perhaps try it as well. I intend to output post-transformed data from prior months into a CSV file, lets call this file "Post-Transformed Historical Data.csv". In PowerQuery I'll only transform data for the current month and import the historically data from Post-Transformed Historical Data.csv which would not require any transformation. My thought process is if I can minimize the number of transformations required every time I refresh the data, then it should move quicker.

[@vansree]

@@ttilta I used power shell to do that transformation and the outputs are used for power bi source The business does not want this extra step of fetching, downloading, transforming I even used table.buffer and list.buffer techniques It all works great for .5gb and after that it is slow Thanks

[@oxnardmontalvo7749]

I don't know why Microsoft is doing that. I tried to pull in a csv that is on the 5th level of folders (site/folder1/folder2/folder3/folder4/) and it would take like 30 minutes to even update the query in the editor, to list all the binaries etc. No one can work with that. The goal was to build an Excel File that is stored on sharpoint, and it always reads the newest csv from a folder. There will be a new file every day. I couldn't get it to work because powerquery never stopped loading. It tries to putll in ALL the folders and files first, before I can start to narrow it down. Why? There's roughly a million documents to go through, before I get to my csv.

[@Vivek-j2t2z]

I have been doing it from last 2 years

[@reanalytics1863]

If I know power query then Chandeep thought me.

[@TheLudvas]

There is nothing about Connecting Sharepoint Excel to Power Bi as title stating..

[@celinaguerreropicon9685]

You can do it through power query in power bi, it's exactly the same way

[@peggymartes6054]

Appreciate it but I am here to learn, but you speak way too fast for me to understand 😞

[@SM23Studio]

I prefer sharepoint.contents method - to me it gives faster result, and easier navigation than filtering to folder/files, etc.

[@RichardJones73]

I was doing this quite easily until Excel power query started to not read the CSV files properly and decided to interpret it as gobbledygook. I did the exact same power query but changed the source to internal drives and it was fine