Get only relevant Rows when Grouping Data-CodePudding

I have data with reference that I wish to group and extract the highest and most common values whilst retaining the relevant references:

The table in green is what I have achieved so far:

M Code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CAS", type text}, {"Type ", type text}, {"Value ", type text}, {"Ref", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"CAS", "Type "}, {{"Value Most Common", each List.Mode([#"Value "]), type nullable text}, {"Ref", each Text.Combine([Ref], ", "), type nullable text}, {"Value Lowest", each List.Max([#"Value "]), type nullable text}})
in
    #"Grouped Rows"

The problem I am having is how best to separate out the References for The Most common and Lowest values. Despite multiple attempts, I just can't get it right. The problem arises because by Splitting columns by Value the references become linked however judging from the input this must be possible. I suspect I need some additional step before grouping.

Additionally, I am unsure how to use grouping to capture Modes where there is a tie between most common values. List.Modes returns a List but again I a running into errors. Currently, I only return the single mode. This is a lesser issue but would be nice as with 24,50 in row 3 of desired.

Data: 
CAS Type    Value   Ref
77-92-9 NOAEL   1200    WebNet
77-92-9 NOAEL   1200    Wiki
77-92-9 NOAEL   4000    ECHA
77-92-9 DNEL    500 RB Data
25265-71-8  DNEL    51  WebNet
25265-71-8  DNEL    24  ECHA
25265-71-8  DNEL    24  ECHA
25265-71-8  DNEL    50  ECHA
25265-71-8  DNEL    50  ECHA
25265-71-8  DNEL    10  ECHA
25265-71-8  NOAEL   200     OECD
106-24-1    DNEL    13.75   ECHA
106-24-1    DNEL    13.75   ECHA
106-24-1    NOAEL   300     RIFM
106-24-1    NOAEL   550     ECHA
106-24-1    NOAEL   50  SAM
106-24-1    NOAEL   50  RIFM
128-37-0    NOAEL   25  ECHA
128-37-0    NOAEL   25  ECHA
128-37-0    NOAEL   25  SAM
128-37-0    ADI 0.3     MMMD
128-37-0    ADI 0.25    JECFA
128-37-0    ADI 0.25    EFSA
60-12-8 DNEL    5.1     ECHA
60-12-8 NOAEL   385     RIFM

CodePudding user response：

It's all in the grouping:

Edited to account for multiple modes as seen after your edit

let
    Source = Excel.CurrentWorkbook(){[Name="Input"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CAS", type text}, {"Type", type text}, {"Value", type number}, {"Ref", type text}}),

    #"Grouped Rows" = Table.Group(#"Changed Type", {"CAS", "Type"}, {
        {"Most Common", each Text.Combine(List.Transform(List.Modes([Value]), each Text.From(_)),", "), type text},
        {"Ref", (t)=>Text.Combine(List.Distinct(Table.SelectRows(t, each List.Contains(List.Modes(t[Value]),[Value]))[Ref]),", "), type text},
        {"Max", each List.Max([Value]), type number},
        {"Ref2", (t)=>Text.Combine(List.Distinct(Table.SelectRows(t, each [Value]=List.Max(t[Value]))[Ref]),", "), type text}
       })

in
    #"Grouped Rows"

Data changed to demo multiple Refs with Multiple Mode