Страницы: 1
RSS
Фильтрация таблицы в query из excel
 
Добрый день уважаемые коллеги.

Подскажите пожалуйста, возможно кто то сталкивался с такой задачей.

У меня есть таблица данных и соответственно она загружена в MS PowerQuery. Таблица содржит данные в разрезе определённых дат, к примеру продажи по дневно.
Мне необходимо сделать так, что бы можно было в самом екселе выбрать месяц начала периода и затем нажать "обновить" и таблица в query отфильтровала данный месяц и вернула уже таблицей применив данный фильтр.

Пример во вложении.

Возникла данная необходимость из за того, что в реальном отчете у меня более 200 тыс строк, и всех их выгружать нереально, файл очень как вы понимаете висит и много весит, но и каждый месяц руками заходить в запросы и менять условия фильтра ( запросов много) коллегам не удобно, хотелось бы выбрать месяц в ячейке в екселе, и далее просто обновить запрос, без ручных корректировок в самом запросе..
 
Доброе время суток.
Как-то так
 
И в дополнение немножко обучающего материала.
Вот горшок пустой, он предмет простой...
 
Андрей спасибо большое. Так же спасибо за ссылку, изучу обязательно, так как проблема имеет прикладной характер.

Если можно, помогите понять ваш код.

Код
Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    monthes = Excel.CurrentWorkbook(){[Name="Monthes"]}[Content],
    fromMonth = Table.SelectRows(monthes, each [Месяц] = Excel.CurrentWorkbook(){[Name="fromMonth"]}[Content]{0}[Column1]){0}[#"№"],
    toMonth = Table.SelectRows(monthes, each [Месяц] = Excel.CurrentWorkbook(){[Name="fromMonth"]}[Content]{0}[Column1]){0}[#"№"],
    result = Table.SelectRows(Source, each Date.Month([Дата]) >= fromMonth and Date.Month([Дата]) <= toMonth),
    #"Changed Type" = Table.TransformColumnTypes(result,{{"Дата", type date}, {"Марка", type text}, {"Кол-во продано", type number}})

Эта часть ясна - добавляется таблица с месяцами из excel для целей слияния.
Код
Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    monthes = Excel.CurrentWorkbook(){[Name="Monthes"]}[Content],

Дальше идут две одинаковых строки (на сколько я понял для определения диапозона - переменные), есть ли кнопочный аналог данного действия?
Код
fromMonth = Table.SelectRows(monthes, each [Месяц] = Excel.CurrentWorkbook(){[Name="fromMonth"]}[Content]{0}[Column1]){0}[#"№"],
    toMonth = Table.SelectRows(monthes, each [Месяц] = Excel.CurrentWorkbook(){[Name="fromMonth"]}[Content]{0}[Column1]){0}[#"№"],
Правильно я понимаю, что тут есть две паралельных столбцы, где нужный столбец филтьтруется по столбцу из таблицы с месяцами? Если в строках выше код возвращает номер месяца, то как в этой строке код определяет именно номер месяца из даты (Date.month ? )? Есть ли кнопочное решение данного кода, что бы понять что происходит и потом тиражировать на реальный отчет?
Код
result = Table.SelectRows(Source, each Date.Month([Дата]) >= fromMonth and Date.Month([Дата]) <= toMonth),

Что бы внедрить данный иснтрумент на реальном отчете, могу я после всего существующего уже кода в квери, добавить данный код ниже, вконец общего кода существующего запроса?

Код
monthes = Excel.CurrentWorkbook(){[Name="Monthes"]}[Content],
    fromMonth = Table.SelectRows(monthes, each [Месяц] = Excel.CurrentWorkbook(){[Name="fromMonth"]}[Content]{0}[Column1]){0}[#"№"],
    toMonth = Table.SelectRows(monthes, each [Месяц] = Excel.CurrentWorkbook(){[Name="fromMonth"]}[Content]{0}[Column1]){0}[#"№"],
    result = Table.SelectRows(Source, each Date.Month([Дата]) >= fromMonth and Date.Month([Дата]) <= toMonth),

Изменено: bobyjoy - 06.11.2019 11:20:53
 
Цитата
bobyjoy написал:
есть ли кнопочный аналог данного действия?
Смотрите видео по моей ссылке. Там все расписано как генерить код кнопками, а потом модифицировать его вручную.
Цитата
bobyjoy написал:
Что бы внедрить данный иснтрумент на реальном отчете
Для этого необходимо пример данных предоставлять в максимально приближенной структуре к реальным данным, потом меньше геморроя с адаптацией, тем более если знаете, что решение вам предложат такое, которое вы скорее всего сходу не разберете.
Изменено: PooHkrd - 06.11.2019 11:26:27
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал:
тем более если знаете, что решение вам предложат такое, которое вы скорее всего сходу не разберете.
Привет, Алексей.
Ну, я всё же гуманный - в стиле тёзки не пишу, хотя можно было бы завернуть - чтобы побудить ТС учиться, учиться и ещё раз учиться :)
 
Андрей VG, то как ваш тезка код заворачивает, я сам иной раз без поллитры разобрать не могу. А ведь я не употребляю! И от этого становится еще печальнее за упущенные возможности к обучению. :D
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал:
я сам иной раз без поллитры разобрать не могу
Порезвимся? Насколько помню, любимый стиль Inexsu также, только в VBA.
Код
((be) => Table.SelectRows(Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content], each Date.Month([Дата]) >= be[b] and Date.Month([Дата]) <= be[e]))
(
    ((s, f, t) => [b = s{[Месяц = f]}[#"№"], e = s{[Месяц = t]}[#"№"]])
    (
        Excel.CurrentWorkbook(){[Name="Monthes"]}[Content],
        Excel.CurrentWorkbook(){[Name="fromMonth"]}[Content]{0}[Column1],
        Excel.CurrentWorkbook(){[Name="toMonth"]}[Content]{0}[Column1]
    )
)

Заодно подправлю ошибку, bobyjoy похоже не заменил
Код
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    monthes = Excel.CurrentWorkbook(){[Name="Monthes"]}[Content],
    fromMonth = Table.SelectRows(monthes, each [Месяц] = Excel.CurrentWorkbook(){[Name="fromMonth"]}[Content]{0}[Column1]){0}[#"№"],
    toMonth = Table.SelectRows(monthes, each [Месяц] = Excel.CurrentWorkbook(){[Name="toMonth"]}[Content]{0}[Column1]){0}[#"№"],
    result = Table.SelectRows(Source, each Date.Month([Дата]) >= fromMonth and Date.Month([Дата]) <= toMonth),
    #"Changed Type" = Table.TransformColumnTypes(result,{{"Дата", type date}, {"Марка", type text}, {"Кол-во продано", type number}})
in
    #"Changed Type"
Изменено: Андрей VG - 06.11.2019 12:51:44
 
Андрей, когда тестировал, заметил, что выбирается только 1 мес
 
Добавил данную часть кода к реальной задаче и все сработало так же.
Спасибо еще раз)

Код
monthes = Excel.CurrentWorkbook(){[Name="Monthes"]}[Content],
    fromMonth = Table.SelectRows(monthes, each [Месяц] = Excel.CurrentWorkbook(){[Name="fromMonth"]}[Content]{0}[Column1]){0}[#"№"],
    toMonth = Table.SelectRows(monthes, each [Месяц] = Excel.CurrentWorkbook(){[Name="toMonth"]}[Content]{0}[Column1]){0}[#"№"],
    result = Table.SelectRows(#"Замененное значение", each Date.Month([Period]) >= fromMonth and Date.Month([Period]) <= toMonth)
in
    #"result"
Страницы: 1
Наверх