I can’t believe it’s finally here! A way to have Excel live in OneDrive and access it from Power BI nearly live! We can officially short cut files to our OneLake from both SharePoint and OneDrive! I am super excited about this feature, and I hope you are too. This feature plus User Data Functions allows us to not only have data from Excel in our reports but keep it as fresh as needed. Imagine having budget allocations that you want to adjust right before or during a meeting. Now you can! You can edit a file in Excel and hit one button to see the new numbers in your report. In the past, we relied on 3rd party services or Power Apps licensing to accomplish this sort of experience. Now we can just use Excel, an old data friend.
Please note, THIS IS IN PREVIEW AND VERY NEW. I’ve included a ton of screenshots, but please be advised these may not be entirely reflective of the GA reality once this feature is released. My example uses a OneDrive folder, but you can easily do this with SharePoint as well! One caveat, you will need a Fabric capacity. This does work with a trial capacity.
- Creating the ShortCut in OneLake to OneDrive Folder
- Connecting to the File in Power BI
- Creating a Refresh Schedule in Power BI Service
- Optional – creating a manual refresh button using Translytical Task Flows
- Additional Resources
Creating the ShortCut in OneLake to OneDrive Folder
1 – Navigate to OneDrive online: https://onedrive.live.com/login
2 – Select the settings gear in the top right corner and select “OneDrive settings”.
3 – On the left-hand panel, select “More Settings” then scroll all the way down to the Diagnostic Information. From there, copy the OneDrive web URL. This is what we will use in Fabric to make the short cut.
NOTE – you will need to delete everything in that URL after the _com (in the screenshot below that’s “_layouts/15/onedrive.aspx”.
4 – Navigate to a lakehouse in Fabric where you would like to access the content from.
5 – Hit the Get data drop down then select “New shortcut”
6 – Choose the OneDrive option. As of this post, it is currently in Preview and says “OneDrive (Preview)” on the button.
7 – Create a new connection. The Site URL will be what you copied from the OneDrive settings. I recommend renaming the connection to something like “[Name]’s OneDrive” so it’s clear where the data comes from.
8 – Now that we have a connection, you can point the shortcut to any folder in your OneDrive! Please note, it will grab all children folders inside as well as files. Be mindful of what you actually want to share within OneLake. On the bright side, this means you only need one shortcut per folder hierarchy which makes this much easier if you have files in multiple subfolders you want to share.
9 – Once you have a folder selected, it may give you an option to transform data (may just be me with CSVs and JSON files in my folders lol). You can skip this by hitting “previous” then “skip” or “Revert Changes” at the top. If you want to transform your CSVs to delta tables, simply hit next. I haven’t played around with these auto transformations yet, so if you have notes let me know! Anything with “Auto” and “Preview” scares me, so I stay away until at least the “Preview” is gone haha. Also, currently this feature does not work. Your shortcut will simply disappear into the void. Really excited to see where this ends up in the future though!
10 – Hit “Create” and boom! You can now access files from your OneDrive inside OneLake! It may take a couple of refreshes on your browser and a few seconds, but then you can go to the Files section of your lakehouse and see the files/folders in your shortcut. NOTE – at first it will show the folder title in your “Tables” section. That means it’s working. Try refreshing your browser (and give it a couple of minutes) and it will pop up in your files section. It does flash a share warning to you, don’t worry about that and just give it a bit to load in the right section. The time it takes is directly proportionate to the amount of information/size of files you’re dropping. If you look at my screenshot, you can see I have one file that’s over 4 GB. Probably not the best folder to pull in (should have gone one level deeper to avoid that file since all I want is in the CSVs folder), but I wanted to see if it can handle it.
If needed, hit the three dots next to your folder and manually move it to files section.
You can see the date modified matches what’s in your OneDrive! Now to test the syncing, let’s make a change to the dim_customer.csv.
Original view:
Change made, took 40 seconds to sync from my laptop to OneDrive then about instantly it was available in Fabric (by the time I moved to that tab and refreshed my page it was there!).
Holy cow! This is a game changer! No longer do folks need to upload files manually using the OneLake explorer (very buggy in my experience). Now you can just short cut it in and allow your ETL process to always grab the latest version that’s been shortcut to OneLake!!
So that brings us to the next phase, how does this work with Power BI? Can we finally have a “live” experience with Excel file data in Power BI?
Connecting to the File in Power BI
1 – Open up your Power BI file. This should work in both the Power BI Desktop and the web Power BI experience, but my demo will use the Desktop.
2 – Connect to the lakehouse with our files. To connect to the files within a lakehouse, we’ll have to do a custom query since the main lakehouse connector only allows you to pull tables/views. Thankfully, a connector does already exist, it’s just not in our standard Get Data options. Create a blank query, then use the code below with your workspace and lakehouse id. To find the IDs, grab from the URL (https://app.fabric.microsoft.com/groups/WORKSPACEID/lakehouses/LAKEHOUSEID?experience=fabric-developer).
let
workspaceID = "YOUR WORKSPACE ID",
lakehouseID = "YOUR LAKEHOUSE ID",
Source = Lakehouse.Contents(null){[workspaceId= workspaceID]}[Data]{[lakehouseId =lakehouseID]}[Data],
Files_Folder = Source{[Id="Files",ItemKind="Folder"]}[Data]
in
Files_Folder3 – Navigate to your file by clicking on the link under “Data” (likely called “Folder”) > then the link under “Content” (also probably called “Folder”) > then the link under “Content” that ties to the file you want to open (mine was called “Binary” for the file I wanted). Now interact with it like any other file import in Power Query.
CSVs are relatively simple, so I pulled in an Excel file for the screenshots below to show what it looks like if you have formatted tables in your file. It shows BOTH formatted tables and sheets! Pretty awesome!
Creating a Refresh Schedule in Power BI Service
Now all that is already pretty amazing. We no longer need to mess with crazy links from Excel and can access all our data from the same place – OneLake! But how do we refresh it?
1 – Navigate to the semantic model settings.
2 – Under “Data Source Credentials” there will be a new source called “Lakehouse”. Open the “Edit Credentials” link and authenticate using the credentials you want to be used for the refresh (can be a service account if needed, but MUST be a OAuth2 source). Right now, there’s no way to connect other than OAuth2. Not great if you want to use service principals, but service accounts are still an option.
If you don’t need “live” updates, you can stop here. However, if you’re as patient as I am, then you’ll want a way to trigger a refresh of this data on the fly from within your report. Enter, translytical task flows.
Optional – creating a manual refresh button using Translytical Task Flows
1 – Create a User Data Function item in Fabric. It will be mostly blank because all we really want is a way to trigger the report refresh. This function will accept a name parameter and return a little message alerting the user that report refresh has been kicked off. Don’t forget to hit that “Publish” button in the top right corner to actually have this be live! The publishing process can take a bit, be sure it finishes publishing before looking for it in Power BI.
Here’s the code I’ll be using:
import datetime
import fabric.functions as fn
import logging
udf = fn.UserDataFunctions()
@udf.function()
def refresh_report(name: str) -> str:
logging.info('Python UDF trigger function processed a request.')
return f"Welcome {name}! Your report refresh will kick off at {datetime.datetime.now()}!"
2 – Navigate to Power BI Desktop and add blank button. You can add any button, but blank looks cleanest and is easiest to make clear what we want people to do.
3 – Add a Data function action and ensure the “Refresh Report” toggle is on. This is the key functionality we are looking for.
4 – Create a measure to populate the current user’s name automatically.
User = USERNAME()5 – Add that measure to the button by selecting the little fx option next to name.
6 – Configure your button with some text to let people know what it does.
7 – Publish and test! Enjoy your new button! Keep in mind, this will refresh the WHOLE model. My model is fairly small and quick, so not a huge deal. There’s not currently a way to have it only refresh one table using the UI, but if you want to make a more complex UDF notebook, you can have it refresh ONLY the table that’s been impacted. Talk about powerful.
I will make a follow up blog soon that will cover how to adapt this method to only refresh the table you need, so stay tuned and hit the subscribe button for a ping when new blogs are published!
UPDATE – I looked into ways to only refresh one table, but it requires using client secret credentials and cannot use semantic link, mssparkutils, or a large number of other libraries available in other notebooks in Fabric. I’m hoping this will change long-term, but for now please refer to this blog on how to use the REST API in a standard python notebook to refresh Power BI: https://medium.com/@arvind.g90/refresh-smarter-not-harder-power-bi-automation-with-rest-api-python-63923b37c9a6 .
Additional Resources
- https://learn.microsoft.com/en-us/fabric/onelake/create-onedrive-sharepoint-shortcut
- https://blog.fabric.microsoft.com/en-US/blog/turning-everyday-documents-from-sharepoint-and-onedrive-into-analytics-ready-data-with-onelake-shortcuts/
- https://www.sharepointdiary.com/2019/01/sharepoint-online-powershell-get-onedrive-url-of-user.html
- https://mstechtalk.com/five-ways-to-get-onedrive-url-for-a-user-in-microsoft-365/
- https://data-marc.com/2023/08/25/access-onelake-files-from-power-bi-desktop/
Data on Wheels 