User Profile
Lorenzo
Silver Contributor
Joined Sep 04, 2017
User Widgets
Recent Discussions
Re: Stacked Excel Formula
Hey IlirU (It's been a while...). I haven't tried to "debug" your formula but think I saw somewhere: IFERROR( TEXTBEFORE(br, " ", LEN(br) - LEN( SUBSTITUTE(br, " ", "") ) ), br ) TEXTBEFORE last arg. - [if_not_found] - could be used instead: TEXTBEFORE(br, " ", LEN(br) - LEN( SUBSTITUTE(br, " ", "") ),,, br) Cheers Lz.Re: Link 2 sheets, then sort column A on sheet 2 alphabetically and match correct data
The checkboxes on the 1st picture suggests 365 is in use An anonymized sample takes less than 5 minutes to put in place Order of the data in the source Table doesn't matter With 365 that could be something like (once required/target columns are known):Re: Chart updates inconsistently when dynamic array resizes
Things have evolved with Version 2602 - Build 19725.20014 => Revised this discussion title (was: Chart from dynamic array challenge) Chart data range will eventually be properly updated/restored - after 1 to n array resizing - in situations like: but won't - according to attached tests - in situations like: Attached zipped files: 26-02-07_SAMPLE_YearAsDate: [Year] converted as Date + <false blank> replaced with 0 26-02-07_SAMPLE_YearAsText: [Year] converted as Text + <false blank> replaced with 0 26-02-10_TESTS_ChartDynArray (tests conducted by someone else) 2026-02-10 Reported a problem to MSFTRe: Excel Data Model + Databricks (ODBC) - Slicers Causing Major Performance Slowdowns
Hi Won't be able to help at all with this & you did not mention where you Databricks sits (Azure/AWS...) so not sure the following pointers are relevant… Performance issues with multiple slicers in "Analyze in Excel" Databricks ODBC Driver download Looks like version >/= 2.6.15 is the min. in your context Create an ODBC DSN-less connection string for the Databricks ODBC Driver Driver capability settings for the Databricks ODBC Driver Databricks ODBC Driver (MSFT) Create an ODBC DSN for the Databricks ODBC Driver (MSFT) Databricks Community Depending on the ODBC Driver capabilities some little optimizations might be possible… Excel 2508 PivotTable Data Model Slowdown: Power Pivot Regression, Slicer Lag... Workarounds TRUE/FALSE? No idea basically just SELECT * from individual tables. The tables are quite large, with the biggest one having around 5 million rows In such a context wonder if SELECT * is the best option adding slicers slows the report down significantly. I’ve tried suggesting using PivotTable filters instead I would test perf. using the Pivot Charts filters (instead of the slicers). If by any chance perf. are better, would this be acceptable workaround would be the next question to the user Forgive me if this is all irrelevantRe: Excel - single window, more files
Hi CodeKamil I've recently switched to new Excel... You were probably running Excel <= 2010 that was using a Single Document Interface (SDI). Since Excel 2013 the latter uses a Multiple Document Interface (MDI) - see Comparing Single and Multiple Document Interfaces for more info. Is it possible to convince Microsoft that they should find a solution to problem they created? The above article should answer your questionRe: Top n vs. Others in Excel
Hi Anonymous29007 A friend of mine with a different issue is also concerned about the unavailability of the Pivot Auto Refresh functionality. In the meantime she wanted a visual flag to remind users to Refresh the PivotTable when required. It's been implemented with a Green/Red icon above the PivotTable: Due to the hack we put in place to sort the [Brand] as expected on the PivotTable the above solution requires a little customization. Before I spend time on this, are you interested? If you are I need to know if you made changes - other than updating your data in TableSource - to the last wbook I shared (PivotChart_v2). Let me know… Lz.Chart updates inconsistently when dynamic array resizes
Hi (Excel 365 v2601 b19628.20132 Current Channel / Windows 11 25H2) Initial post edited (& cross posted here on Jan 29, 2026) after further investigations In B6 below an array that dynamically resizes according to the 'START Year' & 'TOPN Cat' variables. The Chart is setup as follow: Select an empty cell > Insert 2-D Line chart Right-click > Select Data… > Chart data range > Select the Serie names & Values (C6:G12) Click Edit under Horizontal (Category) Axis Labels > Select the range with the Years (B7:B12) Check of the Chart data range: Changing 'START Year' works no problem: the Chart data range & Horizonal Axis Label range are properly updated Changing 'TOPN Cat' (the array resizes horizontally) screws up the chart: The Chart data range is properly updated but the Series & Axis Label ranges don't update accordingly Q: Am I doing something wrong, facing a limitation or is this something else? Tried to attach the sample file 3 times... it's available at: Dynamic_Chart_Challenge.xlsx Thanks & any question let me know Lz.Re: Stacked Excel Formula
Hi (initial post edited - due credit m_tarler) in H11: =OUT_PARAMETER( D11:D12, H11:H12, I11:I12 ) where OUT_PARAMETER is LAMBDA: =LAMBDA([ref_1],[ref_2],[ref_3], 3 - SUM( IF(ISOMITTED(ref_1), 1, COMPARE_TOPARAMETER(ref_1)), IF(ISOMITTED(ref_2), 1, COMPARE_TOPARAMETER(ref_2)), IF(ISOMITTED(ref_3), 1, COMPARE_TOPARAMETER(ref_3)) ) ) and COMPARE_TOPARAMETER: =LAMBDA(reference, LET( RefValue, TAKE( reference, 1, 1 ), comparerStr, TAKE( reference, -1, 1 ), IF( comparerStr = 0, 1, LET( Operator, TRIM(TEXTBEFORE(comparerStr, {0, 1, 2, 3, 4, 5, 6, 7, 8, 9})), afterOperator, TRIM(TEXTAFTER(comparerStr, Operator)), ComparerValue, VALUE(TEXTBEFORE(afterOperator, " ", , , , afterOperator)), --CHOOSE( XMATCH(Operator, {"<", "≤", "=", "≥", ">"}), RefValue < ComparerValue, RefValue <= ComparerValue, RefValue = ComparerValue, RefValue >= ComparerValue, RefValue > ComparerValue ) ) ) ) ) LAMBDAs are stored in Name Manager of the attached wbookRe: Power Query - How Do I Count a Number of Entries Based on Another Column?
jeaster SergeiBaklan file attachment didn't persist (a current random issue on this site). Trying to attach mine and in case this doesn't work, below is the corresponding query code (with the UI only) where CurrentTable reflects the picture you posted and SummarizedQty being the result (same as the pic. posted on my previous reply) Variant is derived from SummarizedQty - output is as Sergei // CurrentTable let Source = Excel.CurrentWorkbook(){[Name="CurrentTable"]}[Content], ChangedTypes = Table.TransformColumnTypes( Source, { {"Polo Cut", type text}, {"Polo Sizing", type text}, {"Red Polo Quantity", Int64.Type}, {"Blue Polo Quantity", Int64.Type}, {"Tan Polo Quantity", Int64.Type} } ) in ChangedTypes // SummarizedQty let Source = CurrentTable, #"Unpivoted Other Columns" = Table.UnpivotOtherColumns( Source, {"Polo Cut", "Polo Sizing"}, "Attribute", "Value" ), #"Grouped Rows" = Table.Group( #"Unpivoted Other Columns", {"Polo Cut", "Polo Sizing", "Attribute"}, { {"Quantity", each List.Sum([Value]), type number} } ), #"Replaced Value" = Table.ReplaceValue( #"Grouped Rows"," Quantity", "", Replacer.ReplaceText, {"Attribute"} ), #"Renamed Columns" = Table.RenameColumns( #"Replaced Value", { {"Attribute", "Color Polo"} } ) in #"Renamed Columns" // Variant let Source = CurrentTable, #"Unpivoted Other Columns" = Table.UnpivotOtherColumns( Source, {"Polo Cut", "Polo Sizing"}, "Attribute", "Value" ), #"Grouped Rows" = Table.Group( #"Unpivoted Other Columns", {"Polo Cut", "Polo Sizing", "Attribute"}, { {"Quantity", each List.Sum([Value]), type number} } ), #"Replaced Value" = Table.ReplaceValue( #"Grouped Rows"," Quantity", "", Replacer.ReplaceText, {"Attribute"} ), #"Renamed Columns" = Table.RenameColumns( #"Replaced Value", { {"Attribute", "Color Polo"} } ), #"Inserted Merged Column" = Table.AddColumn( #"Renamed Columns", "Polo", each Text.Combine({[Polo Cut], [Polo Sizing], [Color Polo]}, " "), type text ), #"Removed Other Columns" = Table.SelectColumns( #"Inserted Merged Column", {"Quantity", "Polo"} ), #"Reordered Columns" = Table.ReorderColumns( #"Removed Other Columns", {"Polo", "Quantity"} ) in #"Reordered Columns"Re: Formula help
Nice one OliverScheurich Looks like kayem doesn't see the benefits of Structured Tables yet - mentioning the main in case this could make a difference in the future... Dynamic ranges (instead of provisioning i.e. 1K rows when 100 are actually used) = efficiency Using structured references make formula more comprehensive Formulas, cells formatting, conditional formats, data validation… auto. apply to new rows added to the Table If the Table Total Row is activated and contains formulas using structured ref., new rows are picked up auto ...
