Блог

Главная - Вопросы по программированию - Запрос мощности — Максимальная строка, основанная на двух числах

Запрос мощности — Максимальная строка, основанная на двух числах

#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"

решение

Метки: ,