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

a lot of times you're going to be working with Excel or CSV as a data source for your powerbi or Excel power pivot models and you would want those files to be there on SharePoint so that the refreshes can be automated in this video I'm going to talk about that if you have Excel or CSV files and they are kept on SharePoint how could you then connect them to your Excel power pivot or powerbi models using of course power query all right this is going to be fun let's start okay first things first I am here on one drive and that's opened on the web version I've kept a couple of files in this folder called demo files Excel and this is the folder that I would want to connect it either to my Excel or my powerbi power query now the process of connecting the data from SharePoint is going to be absolutely the same no matter whether you work in Excel or you work in parbi but there are a few nuances that I would like to talk about right at the start and let's just make a few mistakes and then you'll realize it to begin with I'm going to open up Excel and in Excel I'm going to open up a blank query so I can just go over to a blank Excel file C click on data say get data from other sources and I'm going to open up a blank query right here once I have the blank query open the function that I'm going to use right at the source step is going to be something like SharePoint do files and inside of that it actually asks me hey what's the URL that you would like to get the access from at this moment I'm going to hop back to my SharePoint site and I'm going to get the URL right from the top contrl C on that I'm going to come back to my Excel and start to feed the URL within the K braces right here I'm going to press contrl V and the entire URL is right now pasted now once I do that it doesn't really read the URL properly kind of gives you an error that it's not able to identify it now what you need to understand is that you only need to paste the root URL not the full URL so at the moment what I'm going to do is I'm going to go ahead and cancel out everything that I have and just probably use the Chand chabra my sharepoint.net it's obviously going to ask me to authenticate my credentials which I can do that I'm going to use the Microsoft account that I have and click on connect it asks me that hey verify your account I can do that click on okay and I can click on connect once you have connected with the SharePoint you're going to see that nowhere I I'm going to see the files that I actually need which is the demo folder so these are all the files that have just shown up and if you just go over to the right you're not going to see that where is a subfolder so you have a folder path in here if I just go in the folder path you can see that this is is obviously my SharePoint it particularly talks about cataloges and everything but I cannot just take a look at the subfolder that I wanted to have now this is primarily because I have connected it to the personal SharePoint account not the SharePoint sites account I'm going to go over to the SharePoint but this time I'm going to go open up my SharePoint sites that URL is going to look something like this Chand chabra sharepoint.net and that is also where I would like to store all my files I'm going to pick up this particular URL which is the correct URL for SharePoint contrl C on that I'm going to hop back to uh Power query and cancel out that my SharePoint URL and instead feed the URL that I just copied in the inverted commas of course and I'm going to get rid of everything that is there after goodly just cancel that out close the inverted commas close the bracket press enter it might just again ask you to authenticate if it does please do authenticate if it doesn't you're good to go and now I can probably take a look at all the files that are there and these are the files that we just spoke about which at the moment we are looking at only Excel files so I'm just going to combine the data of the three Excel files before we combine the data let's just take a look at the folder structure that I have so here are the demo files which is the name of the folder within which there are two subfolders these are all the Excel files which we were just able to see that in the power query window and then I also have a CSV files which are there right here so I'm going to show you the techniques to be able to parse both the files be it Excel or CSV and then we will proceed on from here all right as the first part what we have been able to do so far is we are able to connect to the SharePoint folder the right link and then we are able to take a look at every possible thing which is there in SharePoint now we need to zero it down to the files that we actually need so the way to do that is that on the far right I have the folder path and I can navigate to the folder paths that I would like so in here I can just maybe go ahead and search for something like contains and that that is going to be let's say my Excel files that was the keyword of the folder I can click on okay and now that leaves me with only the sub folder which has Excel files in the name of it and then on the far right I can take a look at all the three Excel files and the files are right here at the moment if you take a look we have all the files in the content folder and sure enough you can click on the combine button to be able to combine all the files but I'd like to have a little more flexibility in order to combine the files and I don't really want to have the helper queries created once I click on the combined button so what I'm going to do is I'm going to pass the file myself so I'll click on the FX to create a new step and essentially what I'm trying to do is I'm using a function in power query so that these binaries are converted into tables and power queries able to read them now I've done several videos on connecting data from a folder which is where you also have the similar screen binaries and how do I parse it I explain the logic in a lot more detail using excel. workbook function I highly recommend that you watch that video in order to understand the technique but I'm going to go a little fast here nevertheless so what I'm trying to do is I'm am just trying to transform this particular column so I'll use the function table. transform columns and I'll start the bracket that's the name of the table and the name of the column is obviously the content column so I'm just going to feed the content column and within the content column what I'm trying to do is I'm trying to read the binary off and convert it into a table so that I can read it and the function to do that is going to be excel. workbook so so I'll say excel. workbook start the bracket underscore to grab the file and then I'll write true to by default promote the headers all right this might seem a lot I've done a few videos in the past and I highly recommend that you watch those videos in order to understand what does all of this mean nevertheless let's keep cruising press enter and this is going to read the tables the binaries and convert them into tables I can then now click on the expand button to expand the tables and check the name prefix all the files are here good to go and that is the data of every single Excel sheet like a workbook like a sheet within that file so that's the uh first sheet data the second sheet data the third sheet data all three different Excel files I can just combine it so I can just say something like hey combine the data of the three tables I can use the function called table. combine so table. combine and I can start the bracket uh use the data column which is this particular column and close the bracket at the end and that's pretty much good to go and that is the data combined from from the Excel files which are there on the SharePoint folder all right now let's just repeat the same process and take a look at the small differences that we will have to account for in case the files are not Excel files but CSV files instead so we've already built this query at the moment the name is bad query 1 I'm going to call this as Excel files and I will duplicate this query because pretty much everything that we have done in this query we would need it once again to get the CSV files but with a few changes so I'm going to right click on the Excel files click on duplicate that becomes Excel files 2 which is going to be instead as CSV files now what I'm going to do is I'm going to go ahead and delete all of the steps that I have done the source Remains the Same so I still want to go up till the SharePoint folder that is nice at the moment I don't really want to find Excel files but instead I want to find the CSV files so I'm just going to change the name of the folder and that in my SharePoint was CSV files I believe the CSV was uppercase and I press enter and I get the two CSV files back a while ago we transformed the do Excel files using the function called excel. workbook but this time the function is going to be CSV do doent that's the function to read the CSV or text files well how do we do that I'm going to click on the FX right here and I'm going to go ahead and say something like table. transform columns and I will say something like hey here is a table that I'm trying to work with which is this entire table and the column that I'm trying to work with is the content column so I will just feed that right in so in the C Braes content and then I'm going to say say that in the content column there are let me just show it to you so I will say something like in the content column there are two binaries I want to pick up the binary par it with the function called CSV do doent so I'm going to say that hey the function is something like CSV do doent use that function and underscore gives me the access to that binary right here um click on okay and that's the table that's the data of the CSV and you can see that that's the data that shows up at the moment the only problem is that the headers of of the data is not promoted and obviously I can do that so that CSV do document needs to be wrapped around another function called table. promote headers to be able to promote the headers and I can just close the bracket in the end uh right here and then press enter and that should just work if I just maybe peek into the table once again you're going to see that the headers are nicely promoted now we have two tables and I can just combine the data of the tables so I can just click on the FX to make another step and I can say that I'm trying to feed the content column so I can just reference the content column c n t NT and then I can say hey the content column contains two tables I just want to combine them the function to do that is table. combine and start the bracket close the bracket in the end press enter and the tables are combined this time however from a CSV now you might want to notice that every column is nothing but a text you can assign the data types and you can move the data further whever you would like all right that's about it getting the data from SharePoint either you have Excel or CFC files we took a look at that don't make the mistake of getting the data from your personal my SharePoint site but instead use the organizational one which is where you'll have a sites and then the site name whatever that might be you feed that you put in your credentials and you'll be good to go also in the end I'd like to give a big shout out about my Dax my power query and the M language courses these are extremely structured and well-designed courses and I talk about the ways you can solve the problem understand the pattern of approaching the problem and then you can take that logic and even apply it to your own problems and be confident in solving them hundreds of students have joined and they have extremely benefited from my course and if you would want to learn powerbi from scratch especially the hard Parts power query taxs and M language I'd highly recommend that you please take a look at my courses it's going to be super awesome that's been it thanks so much for watching I'm going to catch you guys in the next one bye [Music] now [Music] o

Channel: Goodly

Views: 20,568

Keywords: Power BI, Power Query, Excel, DAX, M Formulas

Id: ONACyfMvHSw

Length: 11min 5sec (665 seconds)

Published: Wed Mar 27 2024