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:

Result	Red	Green
Result 1	70	80
Result 2	80	90
Result 3	70	85
Result 4	80	90

<tbody>
</tbody>

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:

Average of Value
	Result 1		Result 2		Result 3		Result 4
Row Labels	Green	Amber	Red	Green	Amber	Red	Green	Amber	Red	Green	Amber	Red
Canada	90.3		40.3		86.5	26.5	94.0		27.0			58.5
China	89.8	75.4	33.7	95.0	84.6	37.3	92.7	77.8	35.3	95.4	85.5	41.1
Croatia			58.0			62.0			55.5	95.0		22.0
France	86.2	71.0	37.0	95.0	83.4	43.4	94.5	77.5	39.9	95.5	83.3	42.3
Germany			24.7	96.0		34.0			35.7		82.0	10.5
Japan	87.3	70.0	48.0	95.0	83.0	42.5	88.0	77.2	17.2	94.0	80.0	39.2
Russia	91.0	76.3	37.1	96.1	86.0	42.4	90.3	75.2	32.7	96.2	88.0	45.5
United Kingdom			19.0			42.0			58.0			59.0
United States		79.0	34.0	100.0	88.0	43.7	92.5	80.3	25.6		85.0	39.2

<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>
</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.