BYROW/BYCOL/MAP Variants for Nested Arrays + BENCHMARK
Hey everyone! I made some simple BYROW, BYCOL, and MAP variants that can return nested arrays, and I also made a BENCHMARK function for performance testing. Here's some code for testing: BYROW⊟ = LAMBDA(array, function, [orient], LET( me, LAMBDA(me, seg, LET( n, ROWS(seg), IF( n = 1, function(seg), IF( orient, HSTACK( me(me, TAKE(seg, INT(n / 2))), me(me, DROP(seg, INT(n / 2))) ), VSTACK( me(me, TAKE(seg, INT(n / 2))), me(me, DROP(seg, INT(n / 2))) ) ) ) ) ), IFNA(me(me, array), "") ) ); I didn’t put a huge amount of effort into polishing this but In my tests on my device, these performed a lot better than using REDUCE + VSTACK for the same kind of thing, so maybe it’ll be useful to someone. Really curious to see how people use it, and if something looks like it should be optimized or changed, say so. I'll update them regularly, fix bugs whenever I can. You can find the rest of them on my Gist pages: https://gist.github.com/Medohh2120/f565516bc636700adf5ba27fd8f0d19e, https://gist.github.com/Medohh2120/d9d04f56d93694aed9d0c49d516f0fbf.
Power Query error 'The key didn't match any rows in the table.' when combining
Hello everyone, I'm trying to merge multiple excel files into one spreadsheet using Power Query, but I receive the following error when doing so: An error occurred in the ‘Transform File’ query. Expression.Error: The key didn't match any rows in the table. Details: Key= Item=Client Lists Kind=Sheet Table= Now a bit of information on my data sheets. The workbooks I'm trying to combine are tabled and also contain 2 tabs each. I'm trying to specifically combine 1 of the tabs from each sheet. In the Power Query navigator, it shows the options of 'Table' and the named tab I want to focus on. I tried to separate the 1 specific tab into one book and ensured all of my books have the exact same headers. When I try to combine I still receive the same error. Do I have to un-table my sheets? Or do I have to create a new book for the specific sheet I'm looking to combine? Please help!
Excel Power Query: editing unloaded query refreshes dependent queries
Hi all There has been a change between version 2601 and 2602 on the Current channel which means that when editing a query in Power Query it will trigger a refresh for all dependent queries. This makes Power Query unusable for any complex queries or large data extraction. Take the below example M code. SourceQuery is not loaded to the workbook or data model. LoadedQuery references SourceQuery and is loaded to the workbook. Query: SourceQuery let Source = #table({"Col1"},{{"x"}}) in Source Query: LoadedQuery let Source = SourceQuery in Source In version 2601 and previous versions, you could edit SourceQuery, 'close and load' Power Query and save your workbook almost instantly because no data was refreshed. In 2602 and later versions editing SourceQuery forces a refresh of LoadedQuery which can be very time consuming but more frustrating for all my complex/larger workbooks it triggers lots of refreshes which never finish and result in my having to end task on Excel (note: refreshing these queries takes less than 10 seconds). Doing an end task on all the mashup containers does not allow me to regain control of the Excel workbook. This means all changes to the workbook are lost. I have had to resort to using a VSCode extension to edit my Power Query without crashing Excel. The builds I have tested are 2601 19628.20166 and 2602 19725.20190. Are there any plans to rollback this change? Other than rolling back my Outlook version or using VSCode are there any methods to edit my queries without crashing Excel? It would also be helpful to understand why such a large change in functionality was not captured in the release notes. Per the release notes there were no changes to Excel in 2602, not even 'various fixes to functionality and performance'. Many thanks, Andrew
Python integrado con excel
Tengo una suscripción de Microsoft 365 Empresa Estándar, ya estoy dentro del grupo de Microsoft Insider 365, tengo habilitado el Canal Beta pero aún así no me esta funcionando Python integrado con excel ya que escribo el código pero no me muestra el resultado, en su lugar me muestra el mensaje "BLOQUEADO" indicando que no tengo la licencia requerida. He hecho de todo lo que me ha salido de consejos en la web, incluso cerré sesión y volví a ingresar pero el resultado es el mismo:Adding measures in a data model at a summary level - how ?
I'm probably not going to describe this very well - but here goes 🙂 I am new to data modeling and am trying to convert old files / pivot tables to new table design/data model structures and at the same time develop some new reports for key stakeholders. The attached file has a table (I know its not optimal in terms of structure - but way better than it was). This table (called Hours_New) collects employee time daily. The time is regular, ot or unpaid. It is also chargeable or non-chargeable (as determined by the column in the table titled SC & CD Combo. There are productivity targets (50% for labourers, 75% for other trades). What I want to be able to do is report the productivity % at various levels (Foreman, Trade, Time Period etc etc etc). When I build pivot tables to try and do this I'm not able to get the results presented as I want them - I've taken a couple of stabs at it, but the results are way too busy & I want to be able to highlight when targets are being missed (on both upper and lower ends). I'm sure its not all that tough to do but I'm spinning my wheels - grrr. The attached file has multiple tabs and I've trimmed the dataset down - its actually close to 500,000 rows and removed most of the other pivot tables not related to what I'm trying to do - any help would be most appreciated !!
A new Excel Think Tank
After nearly 30 years of using Excel commercially, I am now coming to retirement. But before I finally hang up my Excel boots, I have setup a small Excel think tank. The idea being people can send me their issues and I will work with you to build your permanent solution in Excel. I have created a number of solutions from Email Validator, Automatic dashboard creators, Fraud analysis, Auto resume makes, Music Syns (All in Excel), so if give it try.What is this Madness? Weird Average and Sum results
What is this devil math? What am I missing? The darker green value and blue value should match. I checked for truncated decimal values, even rebuilt the sample to ensure nothing was weird about the original cells. This is not an insignificant difference for what I'm doing, and I don't understand why these aren't lining up. I'm sorry if I'm missing something simple, but please, help!
Managing Blank Cells in multiple linear regression analysis.
Hello, I am performing a multiple linear regression analysis using the Analysis Toolpak in Excel 365. I have complete y-data, but the x variables are missing some data points throughout a large data set. I know I can fill in the data with the mean or potentially use a regression analysis to predict data to fill in the blanks; however, I would prefer to ignore the blank data if possible. Is there a way to do this inside the data toolpak?Excel 365 Sheet Views are not working
I have 7 different Sheet views in a shared document, they were working fine until about a month ago. Since then I have tried everything, I deleted them, created them again and they don't work. When I create them they look fine but once my collaborators enter to work on the spreadsheet all the views start showing the default view. The sheet has the black border and the eye in the tab. How can I solve this
