Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Подсчет значений и максимальных серий на Power Query
 
#1
02.10.2019 22:59:16
Добрый день.
Помогите пожалуйста с таким вопросом.
Нужно посчитать количество строк и размер максимальных серий, и реализовать это на Power Query.
 
 
 
#2
03.10.2019 00:07:00
Здравствуте
чего-то я перемудил...
Код
let
    Source    = Table.ToRecords(Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content]),
    Indexed   = Table.FromRecords(List.Generate(()=>[i=0,r=Source{i},n=1],each [i]<List.Count(Source),each [i=[i]+1,r=Source{i},n=if r[a]=[r][a] and r[b]=[r][b] then [n]+1 else 1], each Record.AddField([r],"n",[n]))),
    Grouped   = Table.Group(Indexed, {"a", "b"}, {{"list", each {Table.RowCount(_),List.Max([n])}}}),
    Columns   = List.Distinct(Grouped[a]),
    Pivoted   = Table.Pivot(Grouped, Columns, "a", "list"),
    Transform = List.Transform(Table.ToRows(Pivoted),each {_{0}}&{#table({"Итог"}&Columns, List.Zip({{"Всего","Максимальная серия"}}&List.ReplaceMatchingItems(List.Skip(_,1),{{null,{0,0}}})))})
in  Table.ExpandTableColumn(Table.FromRows(Transform),"Column2",{"Итог"}&Columns)
Изменено: Андрей Лящук - 03.10.2019 07:30:04
 
 
 
#3
03.10.2019 08:52:20
Доброе время суток.
Ещё вариант
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    local = Table.Group(Source, {"a", "b"}, {"local", each Table.RowCount(_)}, GroupKind.Local),
    global = Table.Group(local, {"a", "b"}, {{"Всего", each List.Sum([local])}, {"Максимальная серия", each List.Max([local])}}),
    unpivot = Table.Unpivot(global, {"Всего", "Максимальная серия"}, "Итог", "Значение"),
    pivot = Table.Pivot(unpivot, List.Distinct(unpivot[a]), "a", "Значение")
in
    pivot

P. S.
Цитата
Андрей Лящук написал:
чего-то я перемудил...
Да не, всё хорошо.
Updated
Хотя, размножив данные до 26000 строк, тёзка, так и не дождался - когда закончит считать :(
Изменено: Андрей VG - 03.10.2019 09:29:50
 
 
 
#4
03.10.2019 10:05:04
Спасибо, буду пробовать.
 
 
 
#5
07.10.2019 15:01:03
И снова здравствуйте.
Подскажите пожалуйста добавился столбец дата,
если использую сортировку по дате после запроса к таблице
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
#"Сортированные строки" = Table.Sort(Source,{{"Date", Order.Ascending}}),
    local = Table.Group(#"Сортированные строки" , {"a", "b"}, {"local", each Table.RowCount(_)}, GroupKind.Local),
    global = Table.Group(local, {"a", "b"}, {{"Всего", each List.Sum([local])}, {"Максимальная серия", each List.Max([local])}}),
    unpivot = Table.Unpivot(global, {"Всего", "Максимальная серия"}, "Итог", "Значение"),
    pivot = Table.Pivot(unpivot, List.Distinct(unpivot[a]), "a", "Значение")
in
    pivot
Считает как будто сортировки не было. Что я не правильно делаю?
Изменено: kolyaya - 07.10.2019 15:06:13
 
 
 
#6
07.10.2019 15:52:36
Цитата
kolyaya написал:
Считает как будто сортировки не было
Добавьте после сортировки добавление столбца индекса. Известный глюк - ИИ на марше. :)
 
 
 
#7
08.10.2019 21:42:47
Андрей VG, Сработало только после сортировки сначала по значению, а потом по дате. И соответственно после добавления столбца индекса.
 
 
 
#8
20.10.2019 09:34:04
Обманул, не сработало, при сортировке по столбцу "a", начинает считать но сбивается порядок.
Без сортировки, не срабатывает функция
Код
local = Table.Group(#"Сортированные строки" , {"a", "b"}, {"local", each Table.RowCount(_)}, GroupKind.Local)

В cnолбце local они единицы. Может ли проблема крыться в типе данных?

Можете поянить команды Table.RowCount(_) и GroupKind.Local

Я попытался поискать в спецификации но что-то безуспешно.:(

Приложил пример. может в нем понятней будет.

Изменено: kolyaya - 20.10.2019 14:33:55
 
 
 
#9
20.10.2019 14:25:24
Цитата
kolyaya написал:
Может ли проблема крыться в типе данных?
Проблема может крыться в необъяснении на каком порядке вы хотите искать серии. Как понял на порядке: продукт, дата. По процессному смыслу (как понял) - для каждого продукта на последовательном диапазоне дат продаж определить максимальное количество последовательных дней, когда его цена не изменялась, и количество таких последовательностей таких дней. Если угадал, то действительно ли для вас было так сложно поставить такой вопрос задачи? :)
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица2"]}[Content],
    table1 = Table.RenameColumns(Source[[Дата], [Продукт1], [Сумма]], {"Продукт1", "Продукт"}),
    table2 = Table.RenameColumns(Source[[Дата], [Продукт2], [Сумма]], {"Продукт2", "Продукт"}),
    setNeededOrder = Table.Sort(table1 & table2, {{"Продукт", Order.Ascending}, {"Дата", Order.Ascending}}),
    local = Table.Group(Table.AddIndexColumn(setNeededOrder, "id"), {"Продукт", "Сумма"}, {"local", each Table.RowCount(_)}, GroupKind.Local),
    global = Table.Group(local, {"Продукт", "Сумма"}, {{"Всего", each List.Sum([local])}, {"Максимальная серия", each List.Max([local])}}),
    unpivot = Table.Unpivot(global, {"Всего", "Максимальная серия"}, "Итог", "Значение"),
    pivot = Table.Pivot(unpivot, List.Distinct(unpivot[Продукт]), "Продукт", "Значение")
in
    pivot
 
 
 
#10
20.10.2019 18:41:26
Если честно, мне даже трудно понять что вы написали :) А выразиться так я бы точно не смог.
Спасибо. работает.

Можете пояснить команды Table.RowCount(_) и GroupKind.Local
Я попытался поискать в спецификации но что-то безуспешно.
 
 
 
#11
20.10.2019 20:04:20
Цитата
kolyaya написал:
Можете пояснить команды Table.RowCount(_) и GroupKind.Local
Да не вопрос. Они не тайна за семью печатями :)   Table.RowCount, Aggregating By Local Groups In Power Query.
Успехов.
 
 
 
#12
20.10.2019 21:17:21
Спасибо, пошел учить английский :)
 
 
 
#13
21.10.2019 09:32:48
kolyaya, это, кстати, совсем не обязательно. Можно припасть к такому вот источнику мудроты. Не знаю, есть ли там у Николая про локальную группировку, но про подсчет количества строк в таблице уверен что есть.
З.Ы. посмотрел книжку, про локальную группировку ничего не нашел - придется таки учить ангельский.
Изменено: PooHkrd - 21.10.2019 09:52:21
Вот горшок пустой, он предмет простой...
 
 
 
Страницы: 1
Наверх