Excel Challenge - Pivoting poorly structured data
This is from an ExcelBI challenge. I thought it may be worth while posting my solution here as a demonstration of modern Excel methods. Challenge Like many of such challenges, the natural solution approach is to use BYROW but that creates the usual 'array of arrays' error. Solution: Gradually I am moving to a point at which I have no formulas showing in the workbook other than calls to Lambda functions. In this case, the worksheet formuloa is = PIVOTBYCATEGORYλ(OrderTbl) The function works row by row apportioning the amounts against the listed categories PIVOTBYCATEGORYλ // Groups and pivots table by category = LAMBDA(table, LET( normalised, BYROWλ(table, APPORTIONλ), // Identify fields from normalised table dimension, TAKE(DROP(normalised,,1),,2), category, TAKE(normalised,,1), partCost, TAKE(normalised,,-1), // Pivot by category return, PIVOTBY(dimension, category, partCost, SUM,,0,,0), return ) ); The function APPORTIONλ divides the amount between categories so each record within the source data returns a number of rows APPORTIONλ // Splits by category and assigns costs = LAMBDA(record, LET( category, TOCOL(REGEXEXTRACT(INDEX(record,4),"\w+",1)), amount, INDEX(record,3) / COUNTA(category), year, YEAR(INDEX(record,1)), region, IF(LEN(INDEX(record, 2)), INDEX(record, 2), "Unknown"), broadcast, B∕CASTλ(HSTACK(region, year, amount), category), return, HSTACK(category, broadcast), return ) ); /* FUNCTION NAME: B∕CASTλ DESCRIPTION: Broadcasts the terms of a vector over the shape of a second array */ B∕CASTλ = LAMBDA(vector, array, IF({1}, vector, array)); The key to making the formula work is the function BYROWλ that I wrote to generalise the inbuilt but over-restrictive BYROW function. The PIVOTBY function returned the required crosstab from the normalised data array
Variable "Connecting to Datasource" Load times
Hello, I am running into a strange issue that I am not sure how to solve. I am using Power Query to link to external Excel data files/Sheets and then I process the data within Power Query and also have some Macros that run after the data is refreshed. I am seeing a drastic difference in the amount of time it takes separate users to open/refresh this file (the users have the same internet connection, use the same link to the file on a Shared Drive on our servers, same Excel settings, and have the same hardware). Some users can load and run all Macros in < 15 seconds, while the others take > 10 minutes. I have exhausted all possible causes/reasons that I can think of and am looking for some guidance. Any and all suggestions are appreciated! Thanks, Skywalker_
How to create a multi-tiered percentage-based bar chart?
Hello, I would like to create a multi-tiered percentage-based bar chart based on the data found below (test data evidently). Column B and C add up to 100%, Column D and E add up to 100%, and lastly column F and G add up to 100%. I would like each set of variables to equate to 100% on the graph, as the source data is percentage based anyways, but all be organized in their own separate row on the graph underneath the employee name. I have included a diagram drawn in MS paint to portray the desired output (with an accompanying legend). Thanks in advance! Employee Name Number of Sales Made % Number of Sales Made from Other Competitors % Number of Calls Made % Number of Calls Made from Other Competitors % Number of Individual Employees % (always is 1) Number of competitor employees % John 28.57% 71.43% 30.00% 70.00% 16.67% 83.33% Stacy 41.67% 58.33% 38.71% 61.29% 20.00% 80.00% Richard 47.06% 52.94% 47.06% 52.94% 14.29% 85.71% Andy 42.31% 57.69% 40.48% 59.52% 10.00% 90.00%
Moving a column of text data into 3 columns of data?
I have a column of text data cells 1,2,3,4,5,6,7,8,9 and longer. I want to create 3 column of data to graph and manipulate Cell in Columns. 1,2,3 3,4,5 5,6,7 8,9,10 and longer. So i need to create 3 columns of data from 1 column of data. I am using Mac Excel 16 and I can not make this happen. I have tried all sorts of solutions. Help? Thank you,
Couldn't refresh data types (stocks) - sorry, our server is temporarily having problems.
Not sure if this is a coincidence as the date turned to 2026 but the stockhistory function server is not refreshing the data. Anyone else seeing this issue? My 365 license is still good so don't think it's a license issue. This is the message in Excel - Couldn't refresh data types - sorry, our server is temporarily having problems. We're working to fix it. Tried all suggested fixes (reboot, clear cache, log out/in) Anyone else?
Calculating hours using pivot table
Hi, I'm making a personell planning sheet and I want to calculate the sum of hours teachers give lessons. I have 2 tables and my data is formatted like this (simplified) Lesson name Hours Teacher 1 Teacher 2 Lesson 1 2 Paul Lesson 2 3 Pete Lesson 3 2 Paul Pete Teacher name Max working hours Paul 10 Pete 15 Now I want to create an overview of how many hours each teacher is teaching using a pivot table. This is easy when there is just one Teachter collumn but I need to calculate the sum using both teacher collumns... I need to overview to be something like this: Paul -> Lesson 1 + Lesson 3 = 4 hours Pete -> Lesson 2 + lessen 3 = 5 hours Then the next step is to use a metric or KPI to calculate if each teacher is exceeding their max working hours... Can anybody hlelp me with these problems? Thanks!
