🏨👻 There’s something strange happening at the Fabric Haunted Hotel
The data checked in. But it never checked out. 👀 So, for our Spooktacular edition of Fabric Engineering Connection, we’re opening: 🔎 CASE FILE 404: The Mystery of the Missing Data And we’ve called in three Power BI Expert Witnesses: 🔍 Emily Lisa, Principal GPM 🔍 Vivienne Vereen, Principal Product Manager 🔍 Zoe Douglas, Principal Product Manager They’ll take the witness stand for: New Features, Roadmap & a Peek Behind the Curtain 👻 Partners will hear about the latest Power BI innovations from FabCon and get a peek at what’s coming next. But this won’t be a sit-back-and-watch investigation. Every Fabric Detective will receive digital Case File 404 and collect evidence while listening to the Power BI team. 🔍 And yes...🎁 There will be prizes. 📅 Americas & EMEA: Oct. 28 | 8–9 AM PT 📅 APAC: Oct. 29 | 12–1 AM UTC 🔑 Want a key to the hotel? This experience is for members of the Fabric Partner Community. If your organization is a Microsoft partner and you’re not already part of the community, join us before check-in: 👉 https://aka.ms/JoinFabricPartnerCommunity Already a member? Tag another Microsoft partner who should join the investigation. 👇 🎃 Costumes encouraged. 🏨 Haunted Hotel backgrounds ready. 🔎 Curiosity required. CHECK IN • COLLECT THE EVIDENCE • COMPLETE YOUR CASE FILEOn the Next Fabric Engineering Connection
🚀 On the Next Fabric Engineering Connection: AMA with Amir Netz! Fresh from European Microsoft Fabric + SQL Community Conference with questions, feedback, or a few “wait, tell me more about that…” moments? 👀 The next Fabric Engineering Connection is the perfect place to bring them. 🎤 AMA (Ask Me Anything) with Amir Netz, Technical Fellow & CTO This is our first Quarterly AMA of FY27, and partners will have the opportunity to ask questions directly about: ✨ The announcements and product updates coming out of FabCon Europe 💡 What those updates mean for customers and partner practices 🧭 Fabric’s direction and where partners should be focusing 💬 Your own technical questions, feedback, and real-world customer scenarios One of the best parts of the Fabric Partner Community is getting this kind of direct access to the people shaping the product, so come ready with your questions! 📅 Americas & EMEA: Wednesday, October 21 | 8:00–9:00 AM PT 📅 APAC: Thursday, October 22 | 12:00–1:00 AM UTC 🔒 Fabric Engineering Connection is exclusively for members of the Fabric Partner Community. If you’re a Microsoft partner and haven't joined yet, this is a great reason to jump in. 👉 https://aka.ms/JoinFabricPartnerCommunity See you there. I have a feeling this is going to be a good one! 🔥MGDC for SharePoint FAQ: How to use Excel for analytics
MGDC for SharePoint – Dataset types and features In this post, we’ll discuss how to use Microsoft Excel to consume the data from Microsoft Graph Data Connect (MGDC) for SharePoint. If you’re new to MGDC, get an overview at https://aka.ms/SharePointData. 1. Introduction In most examples on how to analyze the data from MGDC for SharePoint, we use Azure Synapse in combination with Power BI Desktop, which is a very capable tool that can turn your data into coherent, visually immersive, and interactive insights. But keep in mind that, when pulling datasets from MGDC with Azure Synapse, the data becomes available in an Azure storage account as a set of JSON files. From there, you can use many different tools beyond Power BI. In this article, we will focus on how to read and analyze that data using Microsoft Excel. 2. Get the data into Azure storage The first step in the process is to get your data from MGDC. I have a blog post that describes that process at Step-by-step: (Synapse) Gather a detailed dataset on SharePoint Sites using MGDC. Section 1 if that blog shows how to setup the Microsoft Graph Data Connect and section 2 describes how to run an Azure Synapse pipeline to pull the data. If you complete these first two sections, you will end up with your SharePoint Sites data in Azure. You can skip section 3 of that blog, that describes how to get the data into a Power BI dashboard. You will replace that section with the information in this article, which takes the data to Microsoft Excel instead. 3. Bring the data from Azure storage to Excel Next, you need to bring the data in the JSON files from the Azure storage account to your Excel spreadsheet. Fortunately, Excel has the perfect set of tools to bring in that data. You should start with a blank spreadsheet. Select the “Data” tab in the ribbon and use the option to “Get Data”. Then select the option “from Azure” and choose “From Azure Data Lake Storage Gen2”. This will make sure that you’re getting the data in your ADLS Gen2 storage account. > Get Data --> From Azure --> From Azure Data Lake Storage Gen2 Next, you will need to provide the URL to the ADLS Gen2 storage. Provide the full path, with the container and folder in the format: https://account.dfs.core.windows.net/container/folder. The account name is in step 1d of the at step-by-step guide mentioned before. The container and folder names are in step 1f of the guide. You can also find the URL in the Azure Portal, under the Settings/Endpoints section of the storage account. Once you provide the URL and click OK, you will need to authenticate. Select the option to use the storage account key and type in the key you saved in step 1d of the at step-by-step guide. You can find the key in the Azure Portal, under the “Security + Networking” / “Access Keys” section of the storage account. Once you have typed the account key, click “Connect”. At this point, you want to click on the “Transform Data” button to filter for the specific files you want. This will open the “Power Query Editor” window, where you can apply specific steps to filter the data coming in. The easiest way to filter out the metadata files is to scroll right until you find the “Folder Path” column and filter that column for paths that do not contain the word “metadata”. > down arrow --> Text filters --> Does not contain... After that, you can scroll left to the “Content” column and click on the button to “Combine files”. After that, the query will pull in the content of the files and expand all the columns from the JSON. > Content --> Combine files Finally, you can click on the “Close & Load” button to bring the data into a sheet. & Load With that, you can play with the data in Excel. 4. Create PivotTables Once the data is imported into your Excel spreadsheet, you will be able to create data visualizations from it. The most interesting way to do this is to use Excel PivotTables. For instance, here’s a pivot table with storage (Storage Used) by site type (RootWeb.WebTemplate) on the different dates you pulled data (SnapShotDate). See the sample PivotTable below after some adjustments and formatting. 5. Tips Here are a few additional comments. Distinct types of authentication: As you might have noticed, there are multiple ways to authenticate to get access to the Azure storage account. In addition to using a key to the entire storage account as shown here, you could use Azure to grant specific permissions to a user and log in as that user. This will help restrict access to specific folders and grant limited permissions (like read-only). Create custom columns: To help with your data analysis, you might want to add a few custom or calculated columns. You can in many ways, including transformations in Power Query, formulas in the Excel sheet or in the pivot table itself. You can read more about it at How to create custom columns in Power BI (this says Power BI but applies to Excel as well). More than one million rows: If you’re using this to visualize data from the SharePoint Sites dataset, you are likely to have fewer than one million rows, which is the Microsoft Excel limit. If you’re one of the few tenants that will have more than one million sites or if you’re analyzing a larger dataset like Permissions or Files, you can only pull the first one million rows into a sheet. To overcome this, you can load the data into a model and use that to drive your pivot tables. You can read more about it at Loading CSV/text files with more than a million rows into Excel (this says text files but applies to JSON as well). 6. Conclusion I hope this post helped you understand how to use Microsoft Excel to pull JSON data from an Azure storage account used by MGDC for SharePoint. For more information, visit https://aka.ms/SharePointData.
