Pivot Table
Hi everyone, I have an issue with the pivot table. There are filters from slicers and row labels in the table; when I double-click on any category from the table to see the filtered data, Excel fetches all data, not just what I filter on. Like below, I filtered from the slicer, and from the row labels, (Bills) should be between 100,000 and 200,000. I would like to see the (Bills) for (Central) in the (Start), but it gives me 632,478 and bills less than 100,000 and 200,000, not the 3 clients. Even if I tried from (In Progress), it's the same; it brings all data. The issue is only with the Bills column, but other filters come up correctly
Error on regression analysis
Hello everyone, I want to perform a regression analysis on my survey data, but I keep getting this error message: “Regression - The RGP() function returns an error value. Check the input ranges.” I’ve since figured out that it’s due to a specific row, but I couldn’t find any incorrect numbers, letters, or anything else in it that would trigger this message. Does anyone know what might be causing this and how I can fix the problem? Thanks in advance!
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
