Всем привет ! Задача очень банальна и проста.....нужно узнать, в каком магазине есть товар. Данные представлены в видео Код артикула | номер магазина , + есть список всех магазинов.
Мое решение было таковым - соединить артикул и код магазина и ВПРить. Все работает, но.. задача осложняется тем, что в данных более 10.000.000 артикулов ( 10 файлов xls по миллиону строк )
Уверен, что есть какое-то простое решение на базе Access , но для меня это дремуч лес. Во вложении файлик пример. Спасибо всем, кто потратит время.
Dark1589 написал: но фильтровать артикулы лучше всё-равно заранее
В данном случае не думаю что PQ будет иметь преимущества по скорости перед сводной. Так же считаю что вариант Dark1589 не самый оптимальный с точки зрения скорости.
Предполагаю что это один из самых быстрых способов.
Код
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
// Группируем таблицу по коду товара
Grp = Table.Group
(
// На таких объемах данных буферизация - наше все имхо
Table.Buffer(Source),
{"Код товара"},
{
// Добавляя столбец Data, в ячейках которого будут содержаться таблицы
// с колонками, соответствующими именам магазинов где есть данный товар
// и единственной строкой, содержащей во всех колонках "x".
{
"Data",
each Table.Buffer
(
// Нестандартное использование Pivot - вместо агрегации данных
// ставим значение "x" во все колонки (имена магазинов, в которых
// есть группируемый товар.
Table.Pivot(_, List.Distinct([Магазин]), "Магазин", "Код товара", each "x")
)
}
}
),
// Это получение уникального сортированного списка всех магазинов для использования в качестве
// имен колонок в готовой таблице.
// Я создаю его, преобразовывая столбец Data, каждая ячейка которого является таблицей,
// в столбец, содержащий список имен колонок таблицы в ячейке. А дальше путем слияния
// списка списков (мутировавший столбец [Data] я получаю все возможные варианты имен магазинов.
List = List.Sort
(
List.Distinct
(
// Как вариант - Вместо List.Combine использовать List.Buffer(Source[Магазин]).
// То есть тупо вычислить уникальные комбинации из колонки Магазин исходной таблицы
// Вполне может быть быстрее..
List.Combine
(
Table.TransformColumns(Grp, {{"Data", each Table.ColumnNames(_)}})[Data]
)
),
Order.Ascending
),
// А дальше просто разворачиваем столбец Data по полному списку магазинов.
// В тех строках, где встречаются имена колонок, которые есть в в List,
// выставляется значение "x", которое там хранится. Если нет (то есть товара
// не было в магазине - то null)
Expanded = Table.ExpandTableColumn(Grp, "Data", List)
in
// В итоге получаем результат.
Expanded
Проверил на такой таблице из миллиона строк. Выполнение основного запроса заняло 50 секунд.
Код
let
Source = List.Transform({0..10}, each "Магазин " & Text.From(_)),
Max = List.Count(Source) - 1,
Tt = Table.FromRows(List.Transform(List.Random(1000000), each {Number.Round(_, 3) * 10000000, Source{Number.Round(Number.RandomBetween(0, Max), 0)}}), {"Код товара", "Магазин"})
in
Tt
Сводная на миллионе строк сводная обновилась за 10 секунд Ну очевидно же что схожие операции сводная выполнит быстрее.
Правда у сводной будет две проблемы - похабный вид данных (в ячейках суммы). Наверное, решается через Custom-столбец, но не проверял сколько времени в этом случае займет обновление. Ну и второе - из 10 файлов данные тянуть.
Alexey_Spb, Алексей, добрый день. В Ваш файл внес свои данные.. запрос больше 15 -ти минут обрабатывается ( 100,000 +/- строк ) .. пока ничего не выходит
Андрей VG, Добрый день. Спасибо, это работает. Подскажите, пожалуйста, есть ли возможность добавить в расчеты третий столбец ? Файл прикрепил с вашим Table.Pivot
В файле есть визуальное отображение что мне нужно. Спасибо, если поможете.