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!
New free Excel training courses from LinkedIn Learning on Support.Office.com
We have a https://na01.safelinks.protection.outlook.com/?url=https%3a%2f%2faka.ms%2fs06o9u&data=02%7c01%7cfrehop%40microsoft.com%7c731d6fec23324d45ba4108d3e329728a%7c72f988bf86f141af91ab2d7cd011db47%7c1%7c0%7c636101739563244453&sdata=Gm%2flh45cca07qEumOOIIpo7N2MqrfDdFwvqWC3%2b30RI%3d to help you get the most out of Excel 2016. Learn how to enter and organize data, create formulas and functions, build charts and PivotTables, and use other time-saving features. Please let us know what you think of these new courses. They’re free, along with the rest of the courses and videos in the https://na01.safelinks.protection.outlook.com/?url=https%3a%2f%2faka.ms%2fidnptu&data=02%7c01%7cfrehop%40microsoft.com%7c731d6fec23324d45ba4108d3e329728a%7c72f988bf86f141af91ab2d7cd011db47%7c1%7c0%7c636101739563244453&sdata=N9i9EgA8%2fzfVkxn7oUmySrNhMPZh01hnhe8KHIXQDak%3d. Best, Freya Office Newsroom
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, Anupam
Error loading Excel files from OneDrive Personal into Power Query
Suddenly a few days ago I started getting an error when loading any Excel file from OneDrive Personal into Power Query. To download I use Excel.Workbook(Web.Contents("https://onedrive.live.com/download?resid=....&authkey=...." ), null, true ) Access Web content - Anonymous, privacy level - Public. The file is readable by anyone with the link. As a result I get an error: DataSource.Error: HTML data loaded that is not the expected type. You may have entered an invalid URL or provided invalid credentials to the server. Until now everything worked fine with these same files. At the same time I noticed that when I get a URL link on OneDrive via File - Embed - Details, I receive a URL address that is not the same as before "https://onedrive.live.com/view.aspx?resid=....&authkey=...." ("view.aspx" always had to be replaced with "download") . Now the link looks like this "https://onedrive.live.com/view.aspx?resid=.....&migratedtospo=true&redeem=....". And of course, this link does not load data into PowerQuery even if you change "view.aspx" to "download". Please tell me how to fix the error?BinaryFormat.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, AnupamComplex numbers in Excel
Did you know that Excel can handle complex mathematical operations with complex numbers? Complex numbers, expressed as "a + bi," where 'a' and 'b' are real numbers and 'i' is the imaginary unit, can be powerful tools in various scenarios. Think engineering, physics, signal processing, or even financial modelling. Excel offers native support for complex numbers, and you can perform basic operations like addition, subtraction, multiplication, and division just as easily as with real numbers. If you haven't explored this yet, give it a shot! It's as simple as entering a formula like =IMSUM(A1:B1) or =IMPRODUCT(A1:B1) One nifty feature is the ability to convert complex numbers from rectangular to polar form and vice versa. This can be a game-changer in certain calculations, especially when dealing with phase angles or magnitude-based analyses. For this you can make use of the =IMABS(A1) and =IMARGUMENT() functions. As complex numbers are considered to be text within Excel you will need special functions to perform arithmetic with them, such as IMSUM, IMSUB, IMPRODUCT, and IMDIV. Excel can help you plot complex numbers on the complex plane using the XY Scatter chart! you will need the IMREAL and IMAGINARY functions to extract the real and imaginary coefficients from the complex number to use them to plot the numbers. This feature can be invaluable when dealing with complex data sets or analysing the behaviour of complex functions. If you're interested in learning more about complex numbers, I made a video covering all functions that work with complex numbers in Excel: https://www.youtube.com/watch?v=_A2DIUibkmk Have you worked with complex numbers before in Excel? What specific use case did you have? Were there any obstacles you had to overcome?
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
