TABLE.GROUP not preserving all columns

[Anonymous]

My problem is with using the TABLE.GROUP( )

I have 3 columns: Year, Country, Score.

I would like to group by year and for each of those years, I want the country with the lowest score. When I use the group function though, I obviously group by year and I use the MINIMUM operation on the SCORE column. 

The problem is that I lose the country column. Rather than returning something like [Year]: 2020; [Country]: Mexico; [Score] = 5.. which is what I would like to have... I only get the years and the minimum score from each of those years.

In SQL, this would be simple however I cannot seem to find any solutions to this online.

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[Anonymous]

Okay so here is the data:

Year	Country	AvgScore
2019	Thailand	1.903226
2019	Mexico	1.887097
2019	Chile	2.193548
2019	Brasil	2.129032
2019	India	1.958024
2019	China	1.970469
2019	Malaysia	2.068615
2019	Indonesia	1.969183
2020	Thailand	1.929782
2020	Mexico	2.067224
2020	Chile	1.909623
2020	Brasil	1.744011
2020	India	2.132863
2020	China	2.010251
2020	Malaysia	1.973565
2020	Indonesia	1.934887
2021	Thailand	1.725972
2021	Mexico	1.986994
2021	Chile	2.031986
2021	Brasil	2.043935
2021	India	2.154861
2021	China	2.057561
2021	Malaysia	1.930264
2021	Indonesia	2.002616

As you can see, there are 3 different years and various countries from those years. Each of those countries has a score, which represents performance for that country (context not neccessary here). My goal is to pull the worst performing country per year.

So ultimately.. I want to have 3 rows,

row 1: 2019 | Mexico | 1.887

row 2: 2020 | Brasil | 1.744

row 3: 2021 | Thailand | 1.726

*order of the rows isn't important here*

So my approach is to group by year and pull the minimum score from that year. Doing that is simple enough with powerbi's group functionality; however the problem when doing this is that this is what it returns:

row 1: 2019 |  1.887

row 2: 2020 | 1.744

row 3: 2021 |  1.726

This is the expected result as the documentation even says the group function will only return the key columns (year) and the new column (minimum score). So the issue is the country column associated with the minimum score per year is lost. I would like to see which country has the minimum score.

[Ashish_Mathur]

Hi,

Similar question answered here - Determine the top selling location for each product.

Hope this helps.

[MFelix · Accepted solution]

Hi @Anonymous ,

Using you can do that by making the following steps:

• Sort Rows by: Year and by AvgScore (both descending)
• Make a group by year and add:
  • Minimum average
  • All lines

In the query the step will look like this you will then need to change this step to:

= Table.Group(#"Sorted Rows", {"Year"}, {{"Minimun", each List.Min([AvgScore]), type number}, {"Country", each _, type table [Year=number, Country=text, AvgScore=number]}})

This step will give the result you already refered in your post now you need to do some advanced change so add the higleted part to your step:

= Table.Group(#"Sorted Rows", {"Year"}, {{"Minimun", each List.Min([AvgScore]), type number}, {"Country", each _, type table [Year=number, Country=text, AvgScore=number]}, {"MinimumCountry", each List.First([Country]), type text}})

Then you will get the following table:

Check full code below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZZIxbsMwDEXv4jkwSEqixLWdOnTrFmQQEgMRYChAvbS3r+zYCqVONvDAbz5+n88DAcpwGr7uMc0x38orjgKGiIfLqeLP6SddHxsMwYN4Dd/vaZ7Kk0YU42zQ7O07Lml+QlpzNfzItxSfX3QByHaheWceLEuzTZzj77KN0ggcGF0X+8jTckSzYDA7J/jnSuIDaVxd12xPda0NHq7rkYSpya2uOHprAVHDw7XcwVBg04XmXQaBXDOnXNdTGMeui9WuxpZ6Do69qycnnjRWvUpgEavhq1cwWLBmqlewptSuoXItvwNjF3q4Ou9a1roaILZdbHUt4wVj2enyBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Year = _t, Country = _t, AvgScore = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Country", type text}, {"AvgScore", type number}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Year", Order.Ascending}, {"AvgScore", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Year"}, {{"Minimun", each List.Min([AvgScore]), type number}, {"Country", each _, type table [Year=number, Country=text, AvgScore=number]}, {"MinimumCountry", each List.First([Country]), type text}})
in
    #"Grouped Rows"

Check this blog post with further explanations on this setup:

https://radacad.com/grouping-in-power-query-getting-the-last-item-in-each-group

Another option is to do this using a measure and create a visual on dax:

Minimum_By_Year = 
VAR MinimumValue =
    CALCULATE (
        MIN ( Group_DAX[AvgScore] );
        ALLEXCEPT ( Group_DAX; Group_DAX[Year] )
    )
RETURN
    IF ( MinimumValue = MAX ( Group_DAX[AvgScore] ); MinimumValue; BLANK () )

Check result below:

See PBIX file attach.

Regards,

MFelix

[Anonymous]

Okay that was really helpful!

One problem that I can't seem to figure out... so I am successfully pulling the first country from the table; however, the individual tables are not sorted and I can't get them to be sorted.

Here is my full query

let
Source = Excel.Workbook(File.Contents("M:\Work\Consolidated Workstream Data.xlsx"), null, true),
#"Mock Data_Sheet" = Source{[Item="Mock Data",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(#"Mock Data_Sheet", [PromoteAllScalars=true]),
#"Added Index" = Table.AddIndexColumn(#"Promoted Headers", "Index", 0, 1),
#"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "Year", "Region", "Country", "Group", "Topic", "Score"}),
#"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns",{{"Index", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Year", "Country"}, {{"Avg Score", each List.Average([Score]), type number}}),
#"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Avg Score", Order.Descending}, {"Year", Order.Descending}}),
#"Grouped Rows1" = Table.Group(#"Sorted Rows", {"Year"}, {{"Minimum", each List.Min([Avg Score]), type number}, {"Country", each _, type table[Year = number, Country =text, AvgScore = number]}, {"MinimumCountry", each List.First([Country]), type text}})
in
#"Grouped Rows1"

[Anonymous]

Fixed it! I had some previous groupings to achieve the data table that I had posted on this thread and I didn't deal with those appropriately to use your proposed query. Everything looks good now, thank you very much. 

[Anonymous]

@MFelix 

Any idea why when I close and apply the values for minimum country are no longer correct?

[MFelix]

Hi @Anonymous ,

Didn't check that part, but since you are using the group and the table is not expanded you need to add some additional steps.

• Expand the Country Column
• Add custom column with following syntax
  • [Country.Country]=[MinimumCountry]
• Filter out all FALSE values from custom column added previously
• Delete all columns you don't need.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZZIxbsMwDEXv4jkwSEqixLWdOnTrFmQQEgMRYChAvbS3r+zYCqVONvDAbz5+n88DAcpwGr7uMc0x38orjgKGiIfLqeLP6SddHxsMwYN4Dd/vaZ7Kk0YU42zQ7O07Lml+QlpzNfzItxSfX3QByHaheWceLEuzTZzj77KN0ggcGF0X+8jTckSzYDA7J/jnSuIDaVxd12xPda0NHq7rkYSpya2uOHprAVHDw7XcwVBg04XmXQaBXDOnXNdTGMeui9WuxpZ6Do69qycnnjRWvUpgEavhq1cwWLBmqlewptSuoXItvwNjF3q4Ou9a1roaILZdbHUt4wVj2enyBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Year = _t, Country = _t, AvgScore = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Country", type text}, {"AvgScore", type number}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Year", Order.Ascending}, {"AvgScore", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Year"}, {{"Minimun", each List.Min([AvgScore]), type number}, {"Country", each _, type table [Year=number, Country=text, AvgScore=number]}, {"MinimumCountry", each List.First([Country]), type text}}),
    #"Expanded Country" = Table.ExpandTableColumn(#"Grouped Rows", "Country", {"Year", "Country", "AvgScore"}, {"Country.Year", "Country.Country", "Country.AvgScore"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Country", "Custom", each [Country.Country]=[MinimumCountry]),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = true)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Country.Year", "Country.Country", "Country.AvgScore", "Custom"})
in
    #"Removed Columns"

Check PBIX file updated.

Sorry for the mistake.

Regards,

MFelix

[Anonymous]

No problem at all. Much appreciated. 

[MFelix]

Hi @pateshivam,

When selectong the group choose the adavanced option that will let ypu add additional group levels, select the year and country as group and the min value.

Should return expected result.

Check the documentation with further explanations.

https://support.office.com/en-ie/article/group-rows-in-a-table-power-query-e1b9e916-6fcc-40bf-a6e8-e...

Regards,
MFelix

[Anonymous]

This wouldn't work because I don't want to group by country. Im sharing my data in the next person's comments and then you'll see what I mean! Thank you, though.