Thanks for the video Ben, I've managed to get this working, but I've since added an additional column to my data, when the refresh happens the extra column isn't being picked up. Do I have to go back and transform data and republish the report (which will cause a long refresh) or is there another way? Thanks
Hi, it depends on your Power Query, but it sounds like you need to edit the query in PQ and then of course perform a full refresh in order to see the data. It should take too long, unless you have thousands of files...
@@BenHoward_PowerBI thanks. It takes about 2 hours to refresh and sometimes times out. 80m rows of data across 160 excel files, but the additional row is only in 1 file. Cheers
@@Dural1985 - I am unsure as to why you have 160 excel files, but if the data is working for you, then 2 hours to refresh seems a small price to pay - imagine how long it would take you to manually create similar reports without the benefit of Power Automate and Power BI.
How can one import multiple tables? Can it be tables in multiple worksheets within the same workbook? Or does it have to be one worksheet in multiple Excel workbooks contained in one Sharepoint folder?
How to do this is beyond a simple response, however, it can be multiple tables in multiple worksheets, or you could load in multiple Excel files, or any combination of the two.
Hey, great content, thanks for sharing! There are 2 questions here: 1. I have a challenge for this solution. Technically, the excel files is stored in sharepoint, so it can be refreshed. But most time, we modify the excel file very often, the excel file tab name can be changed, PQ will have difficulties to recognize it. We need to maintain PQ. 2. I see the data source from Sharepoint is different than other data source, why do not we need to set up gateway? - it has schedule refreshment button in Power BI service already.
Hi, There isn't much I can do if you choose to change the data source name, so the excel file name, the tab or the table name. This has to be a training issue. Personally I would always try and use a table to use as the source, but I took a short cut and just took the data from the sheet. You don't need a gateway because Power BI can access SharePoint Online content directly. You would need a gateway if you were accessing SharePoint on premise.
Thank you for the nice video! Is there a way to link a folder with several excel files that share the same columns and update those in PowerBI as well?
Hi, I have a slightly different challenge, my workbook is located in sharepoint and I have a power_automate flow that takes in the data from this workbook. The issue is this workbook has external links from which it's getting data. Now unless i keep this workbook open I am not getting any updated data for my flow. How to keep refreshing those links even when the workbook is closed!? If you have any idea please help:)
Yes, I would create a PowerAutomate script that opens and refreshes the Excel file, and incorporate this new script into the current one at the beginning.
It's not a dumb Q at all. Onedrive for Business files are stored in SharePoint, so so long as you initiate the connection as per the video (using the web url) then you can update the file by opening it directly from OneDrive in the Excel Desktop application. Hope this helps...
Thank you for this! However, what happens if you use python to generate csv files each month which contains new records. Is it still possible to update Power BI dashboards with the latest csv file?
Hi! Thanks for the video. Any difference in doing it this way, versus get data from SharePoint folder, and then selecting the exact file? I couldn't find a difference...
This does get data from a sharepoint folder, the point about this video is using the get data from Web connector, which then allows you to set up auto refresh. Because you specify the file and the data in the file, it is a little neater in Power Query, but the end result is the same.
Okay but what if your excel file on sharepoint needs to be refreshed automatically as it contains links to other data sources? Now I still need to get in, refresh the file and save it.
Hi Ben, Its great tutorial, Thanks for sharing. I got a question, i am getting an error while scheduling refresh, "you can't schedule refresh for this dataset because the following data sources currently don't support refresh"
Thank you tgis video helps a stack, i am going to hunt for it butva question if you know, doer power BI have links with power automate that will hopefully allow me to change the name of the source file. Tge challenge is tge data set i am working with is so large i am hitting tge excel row limit. I had to break the data into quarters, create pivots and recount the pivots. Unless I do this all in 1 document (extreme refresh times) i can create each quaters file separately and tgen have 1 final master file do pivot combine. Hope i am making sense thank you
thanks for the question. This is a video which shows how to automate the Power BI refresh from an Excel file stored in Teams/SharePoint. If you want to automatically refresh the Excel file, then this assumes it is populated from another (3rd party) source. I've used Power Automate to do this in the past, an example is here where I automate the refresh of Excel with Microsoft Planner data using Power Automate. ruclips.net/video/-U1Nnj95VMo/видео.html
Power BI does not allow me to establish the link with an error message " We couldn't authenticate the credentials provided." The error persist even when I delete the ?web=1 at the end. Any advice?
Power BI should pop up another window asking you to authenticate, and the credentials should be your organisational credentials. Alternatively, prior to opening the .pbit file, ensure the Power BI desktop is already logged in using the organisational account, by clicking sign in in the top right of the Power BI desktop.
On newer version of Power BI, you'll have a window with different sign-in options. Choose the last option ("Organizational Account"), narrow the "level"/scope in the dropdown a couple down from the root level, then click the "Sign in" button. Keep narrowing the scope if the "Connect" button doesn't work after signing in
been struggling all day ... one view of your video and my problem is solved ... THANK YOU !!!!
So helpful! You explain a complex process very simply and easy to understand. Big thanks! 🙏🏽
You are more than welcome!
Thanks for the video Ben, I've managed to get this working, but I've since added an additional column to my data, when the refresh happens the extra column isn't being picked up. Do I have to go back and transform data and republish the report (which will cause a long refresh) or is there another way? Thanks
Hi, it depends on your Power Query, but it sounds like you need to edit the query in PQ and then of course perform a full refresh in order to see the data. It should take too long, unless you have thousands of files...
@@BenHoward_PowerBI thanks. It takes about 2 hours to refresh and sometimes times out. 80m rows of data across 160 excel files, but the additional row is only in 1 file. Cheers
@@Dural1985 - I am unsure as to why you have 160 excel files, but if the data is working for you, then 2 hours to refresh seems a small price to pay - imagine how long it would take you to manually create similar reports without the benefit of Power Automate and Power BI.
Ben you are a life saver! It was the easiest and quickest way to do this. Thanks a lot!
Glad it helped! - you're welcome :)
do you need to have power bi desktop to make this happen or can you just use power bi service? apologies for the ignorance here, im a new user.
Hi, no problem. You need the desktop in order to connect to Excel file
How can one import multiple tables? Can it be tables in multiple worksheets within the same workbook? Or does it have to be one worksheet in multiple Excel workbooks contained in one Sharepoint folder?
How to do this is beyond a simple response, however, it can be multiple tables in multiple worksheets, or you could load in multiple Excel files, or any combination of the two.
@@BenHoward_PowerBI Ok thanks for the reply!
Hey, great content, thanks for sharing! There are 2 questions here:
1. I have a challenge for this solution. Technically, the excel files is stored in sharepoint, so it can be refreshed. But most time, we modify the excel file very often, the excel file tab name can be changed, PQ will have difficulties to recognize it. We need to maintain PQ.
2. I see the data source from Sharepoint is different than other data source, why do not we need to set up gateway? - it has schedule refreshment button in Power BI service already.
Hi, There isn't much I can do if you choose to change the data source name, so the excel file name, the tab or the table name. This has to be a training issue. Personally I would always try and use a table to use as the source, but I took a short cut and just took the data from the sheet. You don't need a gateway because Power BI can access SharePoint Online content directly. You would need a gateway if you were accessing SharePoint on premise.
@@BenHoward_PowerBI thanks for your quick response, just want to see how you face this kind of situation. Nice contents again, thanks!
Thank you for the nice video! Is there a way to link a folder with several excel files that share the same columns and update those in PowerBI as well?
Yes, this is standard functionality of Power BI.
Hi, I have a slightly different challenge, my workbook is located in sharepoint and I have a power_automate flow that takes in the data from this workbook. The issue is this workbook has external links from which it's getting data. Now unless i keep this workbook open I am not getting any updated data for my flow. How to keep refreshing those links even when the workbook is closed!? If you have any idea please help:)
Yes, I would create a PowerAutomate script that opens and refreshes the Excel file, and incorporate this new script into the current one at the beginning.
Thanks so much for the video. Does OneDrive for business and SharePoint work the same way. Sorry if dumb Q.
It's not a dumb Q at all. Onedrive for Business files are stored in SharePoint, so so long as you initiate the connection as per the video (using the web url) then you can update the file by opening it directly from OneDrive in the Excel Desktop application.
Hope this helps...
Thank you for this! However, what happens if you use python to generate csv files each month which contains new records. Is it still possible to update Power BI dashboards with the latest csv file?
Hi! Thanks for the video. Any difference in doing it this way, versus get data from SharePoint folder, and then selecting the exact file? I couldn't find a difference...
This does get data from a sharepoint folder, the point about this video is using the get data from Web connector, which then allows you to set up auto refresh. Because you specify the file and the data in the file, it is a little neater in Power Query, but the end result is the same.
Okay but what if your excel file on sharepoint needs to be refreshed automatically as it contains links to other data sources? Now I still need to get in, refresh the file and save it.
Have you find a solution for this problem?
Hi Ben, Its great tutorial, Thanks for sharing. I got a question, i am getting an error while scheduling refresh, "you can't schedule refresh for this dataset because the following data sources currently don't support refresh"
Thank you tgis video helps a stack, i am going to hunt for it butva question if you know, doer power BI have links with power automate that will hopefully allow me to change the name of the source file. Tge challenge is tge data set i am working with is so large i am hitting tge excel row limit. I had to break the data into quarters, create pivots and recount the pivots. Unless I do this all in 1 document (extreme refresh times) i can create each quaters file separately and tgen have 1 final master file do pivot combine. Hope i am making sense thank you
Hi, you could easily implement several source files and them combine them in Power Query.
what is this? how do you automatically refresh THE EXCEL FILE
thanks for the question. This is a video which shows how to automate the Power BI refresh from an Excel file stored in Teams/SharePoint. If you want to automatically refresh the Excel file, then this assumes it is populated from another (3rd party) source. I've used Power Automate to do this in the past, an example is here where I automate the refresh of Excel with Microsoft Planner data using Power Automate. ruclips.net/video/-U1Nnj95VMo/видео.html
Tqvm Ben.. Let say I just want show my excel dashboard in sharepoint. How I'm gonna do refresh in sharepoint. Im not using Power BI
Hi, Use the file viewer web part which will allow you to display the Excel file in SharePoint.
This was very helpful, thanks mate!
You're welcome, if you like the channel please subscribe to it.
How can I do this without me having to be logged in or have my PC running?
Once you publish the report and set the refresh, you can switch your PC off. Best to watch the video and you'll see what I mean.
Power BI does not allow me to establish the link with an error message " We couldn't authenticate the credentials provided." The error persist even when I delete the ?web=1 at the end. Any advice?
Power BI should pop up another window asking you to authenticate, and the credentials should be your organisational credentials. Alternatively, prior to opening the .pbit file, ensure the Power BI desktop is already logged in using the organisational account, by clicking sign in in the top right of the Power BI desktop.
On newer version of Power BI, you'll have a window with different sign-in options. Choose the last option ("Organizational Account"), narrow the "level"/scope in the dropdown a couple down from the root level, then click the "Sign in" button. Keep narrowing the scope if the "Connect" button doesn't work after signing in