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!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
Power Query Loads Table With Additional Blank Column
Every time I initially load Power Query output to a worksheet the table is always loaded with an additional column that is not in my data. For example, if the query I build has a table with 5 columns when I click Close and Load for the first time the output has 6 columns where the added column is blank and labeled "Column 1". I believe this only happens when I load it to an existing worksheet. Any idea why this is happening? I am loading the output to an existing blank sheet. Thanks in advance.
