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.

 

[sandy666]

Could you describe your algorithm because example excel file does not contain any queries

 

[peter789]

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

 

[Darkstar_]

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?

 

[peter789]

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

 

[Darkstar_]

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?

 

[peter789]

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

 

[peter789]

Excel table called "criteriaTable":

Result	Red	Green
Result 1	50	80
Result 2	40	70
Result 3	30	60
Result 4	20	50

<tbody>
</tbody>

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!

 

[peter789]

Should have added the result when it's in a Pivot Table. Is this what you are after?

Average of Value	Column Labels
	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	64.0	28.5	86.5		26.5	94.0	36.0	13.5	71.3	33.0
China	89.8	65.1	25.4	87.1	55.4	20.4	81.0	43.3	14.9	75.8	34.6	11.6
Croatia		58.0			62.0		66.0	45.0		95.0	22.0
France	86.2	61.1	28.1	85.6	57.4	23.4	75.8	46.7	10.8	80.2	36.9	5.8
Germany			24.7	96.0	63.0	5.0		45.5	16.0	82.0		10.5
Japan	87.3	60.6	37.8	82.8	58.5	17.8	79.0	35.7	9.3	74.8	29.0	9.4
Russia	91.0	63.4	27.8	85.6	52.8	21.1	80.2	45.7	14.6	74.7	35.1	14.0
United Kingdom			19.0		42.0			58.0		59.0
United States		71.8	26.3	86.3	52.6	20.2	88.4	47.7	14.5	67.6	34.6	4.0

<tbody>
</tbody>

 

[Darkstar_]

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.