Получайте только релевантные строки при группировке данных
У меня есть данные со ссылкой, которые я хочу сгруппировать и извлечь самые высокие и наиболее распространенные значения, сохраняя при этом соответствующие ссылки:
Зеленая таблица — это то, чего я добился до сих пор:
М-код:
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"
Проблема, с которой я сталкиваюсь, заключается в том, как лучше всего отделить ссылки для наиболее распространенных и самых низких значений. Несмотря на многочисленные попытки, я просто не могу понять это правильно. Проблема возникает из-за того, что при разделении столбцов по значению ссылки становятся связанными, однако, судя по входным данным, это должно быть возможно. Я подозреваю, что мне нужен дополнительный шаг перед группировкой.
Кроме того, я не уверен, как использовать группировку для захвата режимов, когда существует связь между наиболее распространенными значениями. List.Modes возвращает список, но снова я сталкиваюсь с ошибками. В настоящее время я возвращаю только одиночный режим. Это меньшая проблема, но было бы неплохо, как с 24,50 в строке 3 желаемого.
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
Ответы 1
Все это в группе:
Отредактировано для учета нескольких режимов после редактирования
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"
Данные изменены на демонстрацию нескольких ссылок с множественным режимом.
@horseyride Спасибо, я опубликовал свой ответ до того, как ОП отредактировал свой вопрос, чтобы показать примеры с несколькими режимами. Сейчас редактирую.
Спасибо большое, мучался с этим целый день!
Я думаю {"Наиболее распространенный", каждый Text.Combine(List.Modes([#"Value"]),", "), введите текст с нулевым значением}, поскольку существует несколько возможностей для наиболее повторяющихся и аналогичные изменения для других См. строку примера вывода 4 вверху справа.