#powerquery
Вопрос:
У меня есть следующая таблица:
Order Nr Number 1 Number 2 Value
100 250 66 100
100 251 67 100
100 252 65 80
100 252 64 60
100 252 63 703
101 250 80 100
101 251 81 100
101 252 85 80
101 252 84 60
101 252 83 703
Теперь я хотел бы использовать Power Query, чтобы получить только 1 строку на заказ на основе максимального числа 1, а затем на основе числа 2. Например, заказ № 100. Максимальное число 1 равно 252. И Максимум в этом диапазоне в номере 2 составляет 65. Поэтому строка со значением 80 должна быть результатом.
Как бы вы сделали это в Power Query?
Комментарии:
1. если вы принимаете заказ № =100, МАКСИМАЛЬНОЕ число 2 равно 67. Как тебе удается получать 65?
2. Моя вина, ты прав
3. Хм. Я неправильно прочитал заголовки (и отредактирую свой ответ), но максимальное число 1 заказа № 100 равно 252. А максимальное количество
Order
Nr 100 иNumber 1
252 составляет 65
Ответ №1:
Сгруппируйте по номеру заказа, верните все строки и максимум столбца с номером 1. Развернуть
Сгруппируйте по номеру заказа и номеру 1, верните все строки и максимум столбца с номером 2. Развернуть
Добавьте пользовательский столбец с формулой, как показано ниже, затем отфильтруйте
=if [Number 1]=[Number1Max] and [Number 2]=[Number2Max] then "keep" else "remove")
полный пример кода
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"Order Nr"}, {{"Number1Max", each List.Max([Number 1]), type number}, {"Data", each _, type table}}),
#"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Number 1", "Number 2", "Value"}, {"Number 1", "Number 2", "Value"}),
#"Grouped Rows1" = Table.Group(#"Expanded Data", {"Order Nr", "Number 1"}, {{"Number2Max", each List.Max([Number 2]), type number}, {"Data", each _, type table}}),
#"Expanded Data1" = Table.ExpandTableColumn(#"Grouped Rows1", "Data", {"Number1Max", "Number 2", "Value"}, {"Number1Max", "Number 2", "Value"}),
#"Added Custom" = Table.AddColumn(#"Expanded Data1", "Custom", each if [Number 1]=[Number1Max] and [Number 2]=[Number2Max] then "keep" else "remove"),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = "keep")),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Number2Max", "Number1Max", "Custom"})
in #"Removed Columns"
Комментарии:
1. Спасибо, это отлично работает! Что происходит, когда записи имеют множество значений/ столбцов? Например, значение 1, значение 2, значение 10. Как бы вы это сделали?
2. Это отлично работает со многими другими столбцами столбцов .. просто отредактируйте шаг расширения, чтобы развернуть дополнительные строки
3. Извините, я имел в виду расширить дополнительные столбцы
4. Если это ответ на ваш первоначальный вопрос, пожалуйста, примите ответ, нажав, чтобы переключить галочку с помощью стрелок вверх/вниз
Ответ №2:
Вы можете сделать это в таблице.Командование группой.
- Группируйте по порядку
- затем последовательные фильтры возвращают нужные значения:
***отредактировано, потому что у меня были неправильные заголовки таблиц
let
Source = Excel.CurrentWorkbook(){[Name="Table10"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{
{"Order Nr", Int64.Type}, {"Number 1", Int64.Type}, {"Number 2", Int64.Type}, {"Value", Int64.Type}}),
//Group by "Order Nr"
//Then extract Maximum Number 1 and sequential filters to get the associated values
#"Grouped Rows" = Table.Group(#"Changed Type", {"Order Nr"}, {
{"Number 1", each List.Max([Number 1]), type nullable number},
{"Result", (t)=> let
filter1 = Table.SelectRows(t, each [Number 1] = List.Max(t[Number 1])),
filter2 = Table.SelectRows(filter1, each [Number 2] = List.Max(filter1[Number 2]))
in
filter2}}),
#"Expanded Result" = Table.ExpandTableColumn(#"Grouped Rows", "Result", {"Number 2", "Value"}, {"Number 2", "Value"})
in
#"Expanded Result"
Ответ №3:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Order Nr", Int64.Type}, {"Number 1", Int64.Type}, {"Number 2", Int64.Type}, {"Value", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Order Nr"}, {{"_1", each List.Max([Number 1]), type nullable number}, {"_2", each List.Max([Number 2]), type nullable number}}),
#"Merged Queries" = Table.NestedJoin(#"Grouped Rows", {"Order Nr", "_1", "_2"}, #"Changed Type", {"Order Nr", "Number 1", "Number 2"}, "Grouped Rows", JoinKind.LeftOuter),
#"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"Value"}, {"Value"}),
#"Sorted Rows" = Table.Sort(#"Expanded Grouped Rows",{{"Order Nr", Order.Ascending}})
in
#"Sorted Rows"