Excel Community: Simplifying Spaces and Labels
Hi all, As you may have noticed already, we as the Excel Team have done a bit of "spring/summer cleaning" for the community. We have received feedback that the number of "spaces" was simply too many at nine, so we have pared things down. Now, there are three community spaces: Excel: this will be the primary place for posting content, as many of the old spaces have been migrated as labels will find a good home here BI and Data Analysis: this will be a good place for posting about tasks and questions that cut across Excel, Power BI, and other topics in this realm Resources and Community: this will increasingly become a place for folks to share sample files and templates with each other Q: What happened to the other spaces that used to exist? A: They have since been rolled up as "Labels", which you can find in the "More Resources" sidebar under "Labels" of any of the three spaces mentioned above. Look for these on the right side: Please let us know if you have any other questions. Thanks for reading!
Office Scripts does not refresh Power BI Query live connections in Excel Online
Hello everyone, I've got the following problem and am nearly going insane because it's causing so much problems 🥲 Summary The Office Scripts method workbook.refreshAllDataConnections() no longer refreshes a live connection to a Power BI Semantic Model in Excel Online. Environment Excel Online Office Scripts Power Automate ("Recurrence" + "Run Office Script") Live connection to a Power BI Semantic Model (Analyze in Excel) Expected behavior Calling workbook.refreshAllDataConnections(); should trigger the same refresh as manually selecting Data → Refresh All in Excel Online. Actual behavior The script completes successfully without any error, but the workbook does not requery the Power BI Semantic Model. Additional observations The same workbook worked correctly for approximately two years. The issue started around mid-July 2026. Manual Data → Refresh All in Excel Online refreshes successfully. Refreshing in Excel Desktop also works correctly. The problem occurs both: when running the Office Script manually from the Automate tab in Excel Online when running the same Office Script from Power Automate ("Run Office Script") This suggests the issue is specific to the Office Scripts implementation of refreshAllDataConnections() rather than the workbook, authentication, or the Power BI connection itself. Steps to reproduce Create an Excel workbook with a live connection to a Power BI Semantic Model. Confirm that manual Data → Refresh All refreshes the data successfully. Create an Office Script containing only: function main(workbook: ExcelScript.Workbook) { // Refresh all data connections workbook.refreshAllDataConnections(); } Run the script. Result The script finishes successfully but no refresh is performed. Expected result The workbook should requery the Power BI Semantic Model exactly as when using the manual Refresh All button in Excel Online.January 2017 updates for Get & Transform in Excel 2016 and the Power Query add-in
Excel 2016 includes a powerful set of features based on the Power Query technology, which provides fast, easy data gathering and shaping capabilities and can be accessed through the Get & Transform section on the Data ribbon. Today, The Excel Team is pleased to announce six new data transformation and connectivity features that have been requested by many customers. These updates are available as part of an Office 365 subscription. If you are an Office 365 subscriber, find out how to get these latest updates. If you have Excel 2010 or Excel 2013, you can also take advantage of these updates by downloading the latest Power Query for Excel add-in. These updates include the following new or improved data connectivity and transformation features: New OLE DB connector. Enhanced “Combine Binaries” experience when importing from any folder. Maximize/Restore buttons in the Navigator and Query Dependencies dialogs. Support for percentage data type. Improved “Function Authoring” experience. Improved performance for OData connector. To learn more about each of these features, please visit Office Blogs.How to Be an Excel Detective: Finding and Highlighting Formulas
Hi everyone, I recently wrote a blog post on some simple, yet powerful, techniques for anyone who works with Excel spreadsheets, especially those with complex data. I wanted to share a summary of it with this community, as it might be helpful to others who are looking to understand and protect their work. The post covers two main things: Quickly Revealing All Formulas: A simple keyboard shortcut (Ctrl + `) or the "Show Formulas" option can instantly reveal all formulas in a worksheet. This is a great way to quickly see how a spreadsheet is structured. Permanently Highlighting Formulas: The article shows how to use the "Find & Select" > "Formulas" feature to select all cells containing formulas and then permanently highlight them with a fill color. This visual cue can help prevent accidental edits and protect your data. Watch This in Action: For a step-by-step guide on how to use these techniques, you can watch the video on my https://www.youtube.com/@BIGurus. 🔗 https://youtu.be/1x-1dbqlWXk You can also read the full article here: https://medium.com/@anandsharad/how-to-be-an-excel-detective-finding-and-highlighting-formulas-fe9d4fdbc1b1 I'd be happy to answer any questions you have or discuss other Excel tips and tricks in the comments!
Shift cells up
Dear Experts, I have a data like below:- and want to delete all the nulls,( shift) all the empty cells up, I can do this using F5-> special , select blank and delete , but with huge data it Crash:- Any solution which is less heavy and not cpu hungry that can do this job? any M code , or PQ UI steps? I tried converting each row in to list and then List.RemoveNulls etc but didn't help.. Attached excel for Reference Thanks in Advance, Br, AnupamBinaryFormat.Binary , BinaryFormat.Byte,BinaryFormat.ByteOrder,BinaryFormat.Group,BinaryFormat.Lengt
Dear Experts, Greetings, I have a data like below( Most interesting is the Column "E"-> rat0BitMap:- The max size of the Column "E" is 11 bits ( which is max numOfPRB == 162), I want to perform the above functions in this Column "F" which is the BIN of Column "E", but getting Errors in all functions, in PQ microsoft page, their example also I can't see. Could you please educate me/Perform the above 5 functions in Column "F", using PQ, Thanks in Advance, Br, Anupam
Power BI hierarchies missing when data model consumed in Excel
I posted this issue in the Power BI forums. https://community.powerbi.com/t5/Issues/Power-BI-hierarchies-missing-when-data-model-consumed-in-Excel/idc-p/2352639#M76224 After internal review, the Power BI team said this is an Excel issue and directed me to post here. If a Power BI model contains a DirectQuery or Dual mode table, and that same model also contains attribute hierarchies, those hierarchies are not accessible when the model is consumed through Excel. Example: Model with single table in import mode containing a hierarchy in Power BI Desktop Published model viewed from Excel. Hierarchy works fine in import mode! Model with single table in DirectQuery mode containing a hierarchy Hierarchy missing in Excel 😞 As we have many Excel users on our Power BI enterprise model and also rely on several hierarchies, this prevents us from optimizing our model with any type of aggregation or hybrid table. We are stuck with import on every table and cannot pursue any advanced refresh or query optimization strategies.Data model relations not working properly in power pivot
Hi when doing relations i had some strange results, although same when i did in power bi it worked i have 2 tables of customers one with unique values & one with duplicates, so when i made the relation from unique customer ids to duplicate customer ids, then when i did the pivot table by using data model, i used the customer ids from unique table & customer preferences from customer_preference table which contains duplicate ids, but it gave wrong results dont know why
