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

[Darkstar_]

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.

 

[peter789]

You should be able to edit in the formula bar or advanced editor.
Have you got View Formula Bar ticked?

 

[Darkstar_]

I do now. Although it still isn't clear for me, so apologies.

I assumed it would be more complex. For example

Result 1 - Greater than 80 = Green, Between 70 and 80= Amber, less than 70=Red
Result 2 - Greater than 90 = Green, Between 80 and 90= Amber, less than 80=Red
Result 3 - Greater than 85 = Green, Between 70 and 85= Amber, less than 70=Red
Result 4 - Greater than 90 = Green, Between 90 and 80= Amber, less than 80=Red

This is what I need, but I fear it is too complex for this to handle, hence why I've had to make multiple queries.

 

[peter789]

I'm not sure where I've missed understanding what you are trying to achieve so I'll go through the steps in a bit more detail.
First, make a table in your Excel worksheet with the criteria for the different rankings, you don't have to define Amber as it is between Red and Green. Give the table a name, I've used criteriaTable.

It looks like this:
[TABLE="width: 210"]
<tbody>[TR]
[TD]Result[/TD]
[TD]Red[/TD]
[TD]Green[/TD]
[/TR]
[TR]
[TD]Result 1[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]80[/TD]
[/TR]
[TR]
[TD]Result 2[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]90[/TD]
[/TR]
[TR]
[TD]Result 3[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]85[/TD]
[/TR]
[TR]
[TD]Result 4[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]90[/TD]
[/TR]
</tbody>[/TABLE]

You can change the values and refresh later if you want.

You've already created a table with the raw data. I've named it dataTable.

Now go to Power Query and create a blank query. Go to Advanced editor and paste the following over the statements that are already there:

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

I've called this criteriaTable, you need to edit the name.

Make another blank query and paste this over the code that appears by default, I have simplified the code so the conditional format can be accessed by double clicking on the conditionalColumn step:

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", each if [Value] >= [Green] then "Green" else if [Value] >= [Red] then "Amber" else if [Value] < [Red] then "Red" else null),
removedCriteria = Table.RemoveColumns(conditionalColumn,{"Red", "Green"}),
sortedResults = Table.Sort(removedCriteria,{{"Area", Order.Ascending}}),
#"Changed Type" = Table.TransformColumnTypes(sortedResults,{{"Ranked Group", type text}})
in
#"Changed Type"

Give the query a name and load it to the Data Model and close the editor.

Back in Excel create a Pivot table from the Data Model. You should get this if I have understood correctly what you are after:

Row Labels

Green

Amber

Red

Green

Amber

Red

Green

Amber

Red

Green

Amber

Red

<colgroup><col style="mso-width-source:userset;mso-width-alt:4169;width:86pt" width="114"> <col style="mso-width-source:userset;mso-width-alt:1755; width:36pt" width="48" span="12"> </colgroup><tbody>
[TD="width: 114"]Average of Value[/TD]
[TD="width: 48"][/TD]
[TD="width: 48"][/TD]
[TD="width: 48"][/TD]
[TD="width: 48"][/TD]
[TD="width: 48"][/TD]
[TD="width: 48"][/TD]
[TD="width: 48"][/TD]
[TD="width: 48"][/TD]
[TD="width: 48"][/TD]
[TD="width: 48"][/TD]
[TD="width: 48"][/TD]
[TD="width: 48"][/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"][/TD]
[TD="class: xl66, align: right"]40.3[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66, align: right"]86.5[/TD]
[TD="class: xl66, align: right"]26.5[/TD]
[TD="class: xl66, align: right"]94.0[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66, align: right"]27.0[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66, align: right"]58.5[/TD]

[TD="class: xl65"]China[/TD]
[TD="class: xl66, align: right"]89.8[/TD]
[TD="class: xl66, align: right"]75.4[/TD]
[TD="class: xl66, align: right"]33.7[/TD]
[TD="class: xl66, align: right"]95.0[/TD]
[TD="class: xl66, align: right"]84.6[/TD]
[TD="class: xl66, align: right"]37.3[/TD]
[TD="class: xl66, align: right"]92.7[/TD]
[TD="class: xl66, align: right"]77.8[/TD]
[TD="class: xl66, align: right"]35.3[/TD]
[TD="class: xl66, align: right"]95.4[/TD]
[TD="class: xl66, align: right"]85.5[/TD]
[TD="class: xl66, align: right"]41.1[/TD]

[TD="class: xl65"]Croatia[/TD]
[TD="class: xl66"][/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"][/TD]
[TD="class: xl66, align: right"]55.5[/TD]
[TD="class: xl66, align: right"]95.0[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66, align: right"]22.0[/TD]

[TD="class: xl65"]France[/TD]
[TD="class: xl66, align: right"]86.2[/TD]
[TD="class: xl66, align: right"]71.0[/TD]
[TD="class: xl66, align: right"]37.0[/TD]
[TD="class: xl66, align: right"]95.0[/TD]
[TD="class: xl66, align: right"]83.4[/TD]
[TD="class: xl66, align: right"]43.4[/TD]
[TD="class: xl66, align: right"]94.5[/TD]
[TD="class: xl66, align: right"]77.5[/TD]
[TD="class: xl66, align: right"]39.9[/TD]
[TD="class: xl66, align: right"]95.5[/TD]
[TD="class: xl66, align: right"]83.3[/TD]
[TD="class: xl66, align: right"]42.3[/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"][/TD]
[TD="class: xl66, align: right"]34.0[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66, align: right"]35.7[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66, align: right"]82.0[/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"]70.0[/TD]
[TD="class: xl66, align: right"]48.0[/TD]
[TD="class: xl66, align: right"]95.0[/TD]
[TD="class: xl66, align: right"]83.0[/TD]
[TD="class: xl66, align: right"]42.5[/TD]
[TD="class: xl66, align: right"]88.0[/TD]
[TD="class: xl66, align: right"]77.2[/TD]
[TD="class: xl66, align: right"]17.2[/TD]
[TD="class: xl66, align: right"]94.0[/TD]
[TD="class: xl66, align: right"]80.0[/TD]
[TD="class: xl66, align: right"]39.2[/TD]

[TD="class: xl65"]Russia[/TD]
[TD="class: xl66, align: right"]91.0[/TD]
[TD="class: xl66, align: right"]76.3[/TD]
[TD="class: xl66, align: right"]37.1[/TD]
[TD="class: xl66, align: right"]96.1[/TD]
[TD="class: xl66, align: right"]86.0[/TD]
[TD="class: xl66, align: right"]42.4[/TD]
[TD="class: xl66, align: right"]90.3[/TD]
[TD="class: xl66, align: right"]75.2[/TD]
[TD="class: xl66, align: right"]32.7[/TD]
[TD="class: xl66, align: right"]96.2[/TD]
[TD="class: xl66, align: right"]88.0[/TD]
[TD="class: xl66, align: right"]45.5[/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"][/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"][/TD]
[TD="class: xl66, align: right"]59.0[/TD]

[TD="class: xl65"]United States[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66, align: right"]79.0[/TD]
[TD="class: xl66, align: right"]34.0[/TD]
[TD="class: xl66, align: right"]100.0[/TD]
[TD="class: xl66, align: right"]88.0[/TD]
[TD="class: xl66, align: right"]43.7[/TD]
[TD="class: xl66, align: right"]92.5[/TD]
[TD="class: xl66, align: right"]80.3[/TD]
[TD="class: xl66, align: right"]25.6[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66, align: right"]85.0[/TD]
[TD="class: xl66, align: right"]39.2[/TD]

</tbody>

 

[Darkstar_]

I think I get it now, apologies.
Are you able to attach a workbook with the pivot table attached, so I can see how it all fits together?

 

[peter789]

I'd love to. However attaching or inserting files seems to be a black art on this forum. I am restricted on Addins. I've tried a pm but cannot find an attach option?
P

 

[Darkstar_]

Black magic indeed. Every other forums seems to manage just fine, but anyways.
I think you have given me a lot to chew on, at work tomorrow I'm going to put what I've learnt here into practice to hopefully produce something useful for us.
Thanks again, if I have any other follow ups, I will post here.