Trying to group 4 columns by percentage. Running out of memory!

Darkstar_

New Member
Joined
Nov 24, 2018
Messages
13
I'm hoping there is a much easier way of doing what I am doing.

Firstly, this dummy document should illustrate things.

We have our raw data (i've made the data by using another larger sheet and have about 7 queries in excel just to make it look like the first tab in the linked sheet).
I then want to average the results by area for a specific criteria.
For example, Green results are above 70, amber is between 50 and 69 and red is below 50.

I managed to do this but it's messy. I ended up creating nearly 10+ queries to achieve this. The second tab of the linked sheet is a rough outline of my endgame. I couldn't be bothered to do all the results for this dummy document, it took so much time. So I ended up only making Result 1. But I want all 5 results in this table.
When I do make it at work, my computer (works PC's aren't the best) grinds to a halt and throws up out of memory errors.

So to achieve something (I think) should be easy to do, I end up having nearly 30 queries going.
I end up duplicating queries, filter results between 70-100, duplicate, change it and repeat for all 5 results. I then finally merge them. I'm pretty sure there is an easier way of doing this, but currently the way I do it, seems to burn out our computers at work.
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Could you describe your algorithm because example excel file does not contain any queries
 
Upvote 0
One approach could be:
Unpivot the results columns.
Create a new conditional column to create the ranking group colour code
Group data by Area, Result, Rank colour code. Aggregate average value.
Load to Data Model
Create Pivot table.

Code perhaps like this. (Note I 'm afraid I haven't bothered to tidy it up).

let
Source = Excel.CurrentWorkbook(){[Name="dataTable"]}[Content],
#"Removed Columns" = Table.RemoveColumns(Source,{"Title"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Result 1", type number}, {"Result 2", type number}, {"Result 3", type number}, {"Result 4", type number}, {"Area", type text}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Area"}, "Result", "Value"),
#"Added Conditional Column" = Table.AddColumn(#"Unpivoted Other Columns", "Rank Group", each if [Value] >= 70 then "Green" else if [Value] >= 50 then "Amber" else if [Value] < 50 then "Red" else null),
#"Reordered Columns" = Table.ReorderColumns(#"Added Conditional Column",{"Area", "Result", "Rank Group", "Value"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Reordered Columns",{{"Rank Group", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type2", {"Area", "Result", "Rank Group"}, {{"Average", each List.Average([Value]), type number}}),
#"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Area", Order.Ascending}, {"Result", Order.Ascending}})
in
#"Sorted Rows"

Peter
 
Last edited:
Upvote 0
That worked rather well. Thank you.
Just proof that there is a lot I still don't know.
You managed to pretty much do what I wanted in one query rather than the 12 I ended up with.
As a side note if anyone can answer it, how can I improve memory usage? For example, I see the option to add to data model, but I never tick it. What advantages are there in adding it to a data model over just not?
 
Upvote 0
Personally I load the results of the query to the Data Model if the returned table is large and could possibly exceed the limit on the number of rows on a sheet, it's also essential if you want to utilise PowerPivot.

Peter
 
Upvote 0
Thanks.

I realised an error in my original question, or rather an omission.
The conditional column works, but to make things more complex, each result type has a different criteria. For example result 1 green = >80
But result 2 is green=>70 and so forth. This varies slightly for amber and red. Can this idea be expanded to accommodate these differences?
 
Upvote 0
A few ways spring to mind:
Make a cludgy if statement at the added conditional columns step. if result =result 1 etc, if result = result 2 etc.
Create a lookup table for the different criteria in a separate query. Merge the two queries then substitute the column names for the fixed values in the conditional formula.
Use a custom function, but I think it best to leave that if you are not fully familiar with M language yet.
If I get a chance I'll have a quick look later.

Peter
 
Upvote 0
Excel table called "criteriaTable":

[TABLE="width: 192"]
<tbody>[TR]
[TD]Result[/TD]
[TD]Red[/TD]
[TD]Green[/TD]
[/TR]
[TR]
[TD]Result 1[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]80[/TD]
[/TR]
[TR]
[TD]Result 2[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]70[/TD]
[/TR]
[TR]
[TD]Result 3[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]60[/TD]
[/TR]
[TR]
[TD]Result 4[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]50[/TD]
[/TR]
</tbody>[/TABLE]

Query named "criteriaTable":
let
Source = Excel.CurrentWorkbook(){[Name="criteriaTable"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Result", type text}, {"Red", type number}, {"Green", type number}})
in
#"Changed Type"


Ranking with variable Criteria:
let
Source = Excel.CurrentWorkbook(){[Name="dataTable"]}[Content],
changedType = Table.TransformColumnTypes(Source,{{"Area", type text}}),
removedColumns = Table.RemoveColumns(changedType,{"Title"}),
unpivotOtherCols = Table.TransformColumnTypes(Table.UnpivotOtherColumns(removedColumns, {"Area"}, "Result", "Value"),{{"Value", type number}}),
mergedQueries = Table.NestedJoin(unpivotOtherCols,{"Result"},criteriaTable,{"Result"},"criteriaTable",JoinKind.LeftOuter),
expandedCriteriaTable = Table.ExpandTableColumn(mergedQueries, "criteriaTable", {"Red", "Green"}, {"Red", "Green"}),
conditionalColumn = Table.AddColumn(expandedCriteriaTable, "Ranked Group" as text, each if [Value] >= [Green] then "Green" else if [Value] >= [Red] then "Amber" else if [Value] < [Red] then "Red" else null,type text),
removedCriteria = Table.RemoveColumns(conditionalColumn,{"Red", "Green"}),
sortedResults = Table.Sort(removedCriteria,{{"Area", Order.Ascending}})
in
sortedResults

Peter
PS I enjoyed the film many years ago. Must look it out!
 
Last edited:
Upvote 0
Should have added the result when it's in a Pivot Table. Is this what you are after?

Average of Value
Row LabelsGreenAmberRedGreenAmberRedGreenAmberRedGreenAmberRed

<tbody>
[TD="width: 141, colspan: 3"]Column Labels[/TD]
[TD="width: 47"][/TD]
[TD="width: 47"][/TD]
[TD="width: 47"][/TD]
[TD="width: 47"][/TD]
[TD="width: 47"][/TD]
[TD="width: 47"][/TD]
[TD="width: 47"][/TD]
[TD="width: 47"][/TD]
[TD="width: 47"][/TD]

[TD="colspan: 2"]Result 1[/TD]

[TD="colspan: 2"]Result 2[/TD]

[TD="colspan: 2"]Result 3[/TD]

[TD="colspan: 2"]Result 4[/TD]

[TD="class: xl65"]Canada[/TD]
[TD="class: xl66, align: right"]90.3[/TD]
[TD="class: xl66, align: right"]64.0[/TD]
[TD="class: xl66, align: right"]28.5[/TD]
[TD="class: xl66, align: right"]86.5[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66, align: right"]26.5[/TD]
[TD="class: xl66, align: right"]94.0[/TD]
[TD="class: xl66, align: right"]36.0[/TD]
[TD="class: xl66, align: right"]13.5[/TD]
[TD="class: xl66, align: right"]71.3[/TD]
[TD="class: xl66, align: right"]33.0[/TD]
[TD="class: xl66"][/TD]

[TD="class: xl65"]China[/TD]
[TD="class: xl66, align: right"]89.8[/TD]
[TD="class: xl66, align: right"]65.1[/TD]
[TD="class: xl66, align: right"]25.4[/TD]
[TD="class: xl66, align: right"]87.1[/TD]
[TD="class: xl66, align: right"]55.4[/TD]
[TD="class: xl66, align: right"]20.4[/TD]
[TD="class: xl66, align: right"]81.0[/TD]
[TD="class: xl66, align: right"]43.3[/TD]
[TD="class: xl66, align: right"]14.9[/TD]
[TD="class: xl66, align: right"]75.8[/TD]
[TD="class: xl66, align: right"]34.6[/TD]
[TD="class: xl66, align: right"]11.6[/TD]

[TD="class: xl65"]Croatia[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66, align: right"]58.0[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66, align: right"]62.0[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66, align: right"]66.0[/TD]
[TD="class: xl66, align: right"]45.0[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66, align: right"]95.0[/TD]
[TD="class: xl66, align: right"]22.0[/TD]
[TD="class: xl66"][/TD]

[TD="class: xl65"]France[/TD]
[TD="class: xl66, align: right"]86.2[/TD]
[TD="class: xl66, align: right"]61.1[/TD]
[TD="class: xl66, align: right"]28.1[/TD]
[TD="class: xl66, align: right"]85.6[/TD]
[TD="class: xl66, align: right"]57.4[/TD]
[TD="class: xl66, align: right"]23.4[/TD]
[TD="class: xl66, align: right"]75.8[/TD]
[TD="class: xl66, align: right"]46.7[/TD]
[TD="class: xl66, align: right"]10.8[/TD]
[TD="class: xl66, align: right"]80.2[/TD]
[TD="class: xl66, align: right"]36.9[/TD]
[TD="class: xl66, align: right"]5.8[/TD]

[TD="class: xl65"]Germany[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66, align: right"]24.7[/TD]
[TD="class: xl66, align: right"]96.0[/TD]
[TD="class: xl66, align: right"]63.0[/TD]
[TD="class: xl66, align: right"]5.0[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66, align: right"]45.5[/TD]
[TD="class: xl66, align: right"]16.0[/TD]
[TD="class: xl66, align: right"]82.0[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66, align: right"]10.5[/TD]

[TD="class: xl65"]Japan[/TD]
[TD="class: xl66, align: right"]87.3[/TD]
[TD="class: xl66, align: right"]60.6[/TD]
[TD="class: xl66, align: right"]37.8[/TD]
[TD="class: xl66, align: right"]82.8[/TD]
[TD="class: xl66, align: right"]58.5[/TD]
[TD="class: xl66, align: right"]17.8[/TD]
[TD="class: xl66, align: right"]79.0[/TD]
[TD="class: xl66, align: right"]35.7[/TD]
[TD="class: xl66, align: right"]9.3[/TD]
[TD="class: xl66, align: right"]74.8[/TD]
[TD="class: xl66, align: right"]29.0[/TD]
[TD="class: xl66, align: right"]9.4[/TD]

[TD="class: xl65"]Russia[/TD]
[TD="class: xl66, align: right"]91.0[/TD]
[TD="class: xl66, align: right"]63.4[/TD]
[TD="class: xl66, align: right"]27.8[/TD]
[TD="class: xl66, align: right"]85.6[/TD]
[TD="class: xl66, align: right"]52.8[/TD]
[TD="class: xl66, align: right"]21.1[/TD]
[TD="class: xl66, align: right"]80.2[/TD]
[TD="class: xl66, align: right"]45.7[/TD]
[TD="class: xl66, align: right"]14.6[/TD]
[TD="class: xl66, align: right"]74.7[/TD]
[TD="class: xl66, align: right"]35.1[/TD]
[TD="class: xl66, align: right"]14.0[/TD]

[TD="class: xl65"]United Kingdom[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66, align: right"]19.0[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66, align: right"]42.0[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66, align: right"]58.0[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66, align: right"]59.0[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]

[TD="class: xl65"]United States[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66, align: right"]71.8[/TD]
[TD="class: xl66, align: right"]26.3[/TD]
[TD="class: xl66, align: right"]86.3[/TD]
[TD="class: xl66, align: right"]52.6[/TD]
[TD="class: xl66, align: right"]20.2[/TD]
[TD="class: xl66, align: right"]88.4[/TD]
[TD="class: xl66, align: right"]47.7[/TD]
[TD="class: xl66, align: right"]14.5[/TD]
[TD="class: xl66, align: right"]67.6[/TD]
[TD="class: xl66, align: right"]34.6[/TD]
[TD="class: xl66, align: right"]4.0[/TD]

</tbody>
 
Last edited:
Upvote 0
Just tried it and it looks good from my end. However the conditional column you added, I can't modify it. I wanted to see how it all works but alas.
I modified the criteria table with the correct percentages and wanted to change the conditional column. The only option it gives me is to delete.
 
Upvote 0

Forum statistics

Threads
1,223,896
Messages
6,175,264
Members
452,627
Latest member
KitkatToby

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top