Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Выборка данных по условию в таблице, построенной функцией Power Query
 
#1
02.08.2019 16:14:43
Доброго дня!
Уважаемые участники форума, прошу помощи в решении следующей задачи:

Можно ли, реализовать фильтрацию данных в таблице построенной функцией Power Query по условию указанному в ячейке (лист "Power Query") сделать на подобии примера, реализованного с помощью формул (лист "пример")
Метод с формулами не подходит из-за необходимости иметь запасные строки для вывода списка, а сводная таблица сама добавляет нужно кол-во строк.
Изменено: e-748 - 02.08.2019 16:18:36
 
 
 
#2
02.08.2019 16:21:04
Вот тут есть описание как затащить параметр из ячейки и передать его в запрос.
Вот горшок пустой, он предмет простой...
 
 
 
#3
13.08.2019 14:51:10
Спасибо за информацию, если не затруднит, могли бы точнее указать куда смотреть, я в этом деле не особо понимаю, только учусь.
 
 
 
#4
13.08.2019 17:06:39
.
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица7"]}[Content],
    Custom1 = Excel.CurrentWorkbook(){[Name="фильтр"]}[Content]{0}[Column1],
    Custom2 = Table.SelectRows(Источник, each ([Код услуги по ЕНМУ] = Custom1)),
    Removed = Table.RemoveColumns(Custom2,{"Код услуги по ЕНМУ", "Наименование услуги"})
in
    Removed
 
 
 
#5
14.08.2019 10:58:08
artyrH Огромное Вам спасибо за помощь, и пример, более менее стало понятно. Единственное, возможно настроить авто обновление таблицы при изменении условия?
Изменено: e-748 - 14.08.2019 10:58:34
 
 
 
#6
14.08.2019 11:07:41
В модуль листа:
Код
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect([фильтр], Target) Is Nothing Then ActiveWorkbook.RefreshAll
End Sub
или если только этот запрос обновлять:
Код
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect([фильтр], Target) Is Nothing Then ListObjects(1).QueryTable.Refresh
End Sub
Изменено: buchlotnik - 14.08.2019 11:37:50
Соблюдение правил форума не освобождает от модераторского произвола
 
 
 
#7
14.08.2019 11:15:13
фантастика, все работает)))) спасибо buchlotnik  
 
 
 
#8
20.08.2019 12:51:32
Здравствуйте!
Скажите, а можно задать в условие не одно значение и несколько?
У меня есть книга эксель с большой базой которая постоянно обновляется, я дроблю ее на несколько других книг по условиям. И на основе каждой делаю разные запросы. Хотелось задавать эти условия сразу в запросах, чтобы не переобновлять в ручную каждый раз книги с базами
 
 
 
#9
20.08.2019 12:54:30
Цитата
mos_art написал:
а можно задать в условие не одно значение и несколько?
можно
Соблюдение правил форума не освобождает от модераторского произвола
 
 
 
#10
20.08.2019 13:09:40
Уже хорошо) А как это сделать?
Судя по всему за фильтр отвечает эта строка:
Код
Custom1 = Excel.CurrentWorkbook(){[Name="фильтр"]}[Content]{0}[Column1]
Но я не могу понять, как ячейке B3 задали название(?) фильтр, и как растянуть его на большее кол-во ячеек
с Power Query я не знаком совсем, запросы и те на уровне интуиции делал
 
 
 
#11
20.08.2019 13:18:24
Цитата
mos_art написал:
Но я не могу понять, как ячейке B3 задали название(?) фильтр, и как растянуть его на большее кол-во ячеек
Вот тут подробно все объяснено, с видео.
Вот горшок пустой, он предмет простой...
 
 
 
#12
20.08.2019 13:19:54
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица7"]}[Content],
    Custom1 = Excel.CurrentWorkbook(){[Name="фильтр"]}[Content][Column1],
    Custom2 = Table.SelectRows(Источник, each (List.ContainsAny({[Код услуги по ЕНМУ]},Custom1) = true)),
    Removed = Table.RemoveColumns(Custom2,{"Код услуги по ЕНМУ", "Наименование услуги"})
in
    Removed
Соблюдение правил форума не освобождает от модераторского произвола
 
 
 
#13
20.08.2019 13:27:23
Спасибо! До этого разобрался что диапазон списка можно редактировать через диспетчер имен, но не понимал почему условие все равно берется только из первой строки, благодаря PooHkrd, понял, что номер строки задается, а с помощью buchlotnik, выяснил что если номер убрать, то будет учитываться весь список
 
 
 
#14
21.08.2019 11:17:40
Доброго времени!
Возвращаюсь за помощью. В настоящее время код выглядит так:
Код
let
      
    Источник = Excel.Workbook(File.Contents("\\путь к файлу с исходными данными.xlsb"), null, true),
    Источник1 = Источник{[Name="Листсданными"]}[Data],
    Custom1 = Excel.CurrentWorkbook{[Name="фильтр"]}[Content][Column1],
    Custom2 = Table.SelectRows(Источник1, each (List.ContainsAny({[Column10]},Custom1) = true)),
    #"Повышенные заголовки" = Table.PromoteHeaders(Custom2, [PromoteAllScalars=true]),
    #"Измененный тип" = Table.TransformColumnTypes(#"Повышенные заголовки",{{"Показатель Н", type text}, {"Регион", type text}, {"Квартал", type text}, {"№ Сегмента", Int64.Type}, {"Тип клиента", type text}, {"Канал", type text}, {"Аттрибут", type text}, {"Сумма", Int64.Type}, {"Наимен аттрибута", type text}}),
    #"Условный столбец добавлен" = Table.AddColumn(#"Измененный тип", "Полугодие", each if [Квартал] = "2Q2017" then "1H2017" else if [Квартал] = "1Q2017" then "1H2017" else if [Квартал] = "4Q2017" then "2H2017" else if [Квартал] = "3Q2017" then "2H2017" else if [Квартал] = "2Q2018" then "1H2018" else if [Квартал] = "1Q2018" then "1H2018" else if [Квартал] = "3Q2018" then "2H2018" else if [Квартал] = "4Q2018" then "2H2018" else "-"),
    #"Условный столбец добавлен1" = Table.AddColumn(#"Условный столбец добавлен", "Год", each if [Полугодие] = "2H2017" then 2017 else if [Полугодие] = "1H2017" then 2017 else if [Полугодие] = "1H2018" then 2018 else if [Полугодие] = "2H2018" then 2018 else "-"),
    #"Условный столбец добавлен2" = Table.AddColumn(#"Условный столбец добавлен1", "Дата", each if [Квартал] = "1Q2017" then #date(2017, 4, 1) else if [Квартал] = "2Q2017" then #date(2017, 7, 1) else if [Квартал] = "3Q2017" then #date(2017, 10, 1) else if [Квартал] = "4Q2017" then #date(2018, 1, 1) else if [Квартал] = "1Q2018" then #date(2018, 4, 1) else if [Квартал] = "2Q2018" then #date(2018, 7, 1) else if [Квартал] = "3Q2018" then #date(2018, 10, 1) else if [Квартал] = "4Q2018" then #date(2019, 1, 1) else "-"),
    #"Переименованные столбцы" = Table.RenameColumns(#"Условный столбец добавлен2",{{"Полугодие", "За Полугодие"}, {"Год", "За Год"}}),
    #"Измененный тип1" = Table.TransformColumnTypes(#"Переименованные столбцы",{{"Дата", type date}})

in
    #"Измененный тип1"

Все работает, в запрос тянутся нужные значения, но происходит это жутко долго, пробовал на массиве данных в пару сотен строк, это заняло около минуты, с увеличением объема увеличивается и время загрузки. У меня же объем данных в в файле с исходными данных составляет несколько сотен тысяч строк и при 10 столбцах.

Что можно сделать для ускорения загрузки?
Изменено: mos_art - 21.08.2019 11:17:51
 
 
 
#15
21.08.2019 11:22:57
Доброе время суток
Цитата
mos_art написал:
Что можно сделать для ускорения загрузки?
Table.Join или Table.NestedJoin
 
 
 
#16
21.08.2019 13:56:42
Андрей VG, не смог разобраться, как понимаю, это операторы слияния таблиц, но какие именно мне нужно таблицы сливать вместе? у меня ведь она и так одна в базе.
 
 
 
#17
21.08.2019 15:24:02
Цитата
mos_art написал:
у меня ведь она и так одна в базе.
Точно? А по шагам смотреть не пробовали? Этот шаг
Код
Custom1 = Excel.CurrentWorkbook{[Name="фильтр"]}[Content]
Это не таблица? Вот ей через указанные выше функции, и фильтруйте свою ту одну таблицу.
P. S. И что у вас за сложность приложить внятный пример, что есть и как должно получиться. Вы большой любитель нарушать правила?
Изменено: Андрей VG - 21.08.2019 15:24:53
 
 
 
#18
21.08.2019 18:44:40
Цитата
Андрей VG написал:
P. S. И что у вас за сложность приложить внятный пример, что есть и как должно получиться. Вы большой любитель нарушать правила?
Андрей VG, вот прикрепляю ссылку на яндекс диск, так как файлы более 100кб.
Файлы
Непосредственно файл источник и рабочий файл, где находится запрос и куда тянутся данные. Значения по которым нужно фильтровать прописываются в столбце "T" в рабочем файле.  
 
 
 
#19
21.08.2019 18:50:43
mos_art, ограничения на файлы - 300 кб, а еще бывают архиваторы. Зачем вы прикалдываете рабочий файл? Приложите файл пример с той же структурой, но десятком строк. Я вот с облаков качать не могу - а так бы уже помог.  ;)
Вот горшок пустой, он предмет простой...
 
 
 
#20
21.08.2019 19:12:10
Цитата
PooHkrd написал:
Я вот с облаков качать не могу
Я дома уже - могу :)
mos_art, вы всегда формируете пример таким образом - пойди туда, не знаю куда, найди то, не знаю что? Какой смысл использовать именованный диапазон, если там нет данных? Да и смысл в именованном диапазоне в данном случае? И какого ляда выкладывать файл с моделью данных в Power Pivot? В новой книге пример нельзя было сделать? Типа нате?
 
 
 
#21
22.08.2019 11:12:30
Андрей VG, при всем уважении, не понимаю вашего негодования.
Вы просили прикрепить пример, что есть и что должно получиться.
Я и взял свои файлы, удалив лишнюю информацию, что бы дать полноценный вариант, поэтому там и осталась модель. (собственно поэтому наверное и размер файла большой). Это было сделало по незнанию, а не "вот пример, сделайте уже".
Power Pivot для меня темный лес, поэтому и прошу помощи.
Благодаря вашему примеру, понял, что было ошибкой использовать целый столбец в качестве диапазона. Хотя я делал так, чтобы не задавать жесткие ограничение по размеру, так как кол-во параметров для фильтрации может меняться. Но умная таблица в вашем примере решает эту проблему, сам я до этого не додумался.
Спасибо вам за помощь, буду пробовать

UPD. На днях появилось время, попробовал. Спасибо большое, Андрей VG, все работает, скорость не пострадала. Избавили от кучу повторяющийся действий
Изменено: mos_art - 18.09.2019 16:04:14
 
 
 
#22
03.03.2020 16:34:03
Доброе время суток, уважаемые форумчане!

По методике https://www.youtube.com/watch?v=_5x-xcUJ0zw  создал функцию fParam  с аргументом Region для ввода в запрос Power Query параметров фильтров с листа EXCEL с последующей встроенной фильтрацией таблицы данных TABLE1.  Все прекрасно работает. НО...

При пустой ячейке значения параметра на листе EXCEL запрос (при отсутствии пустых значений в столбце фильтрации) выдает результирующую пустую таблицу. А хочется в этом случае получить всю таблицу без фильтрации.

Какие возможны варианты решения такой задачи?

Сам я попытался использовать вычисление выражений в языке М с использованием конструкции if then else:
Код
Expression.Evaluate ( if fParam("Region")=null then Table.SelectRows(#TABLE1, each true) else Table.SelectRows(#TABLE1, each [REGIONs]=fParam("Region")))

Естественно ругается, что ошибка с типами данных. А как решить проблему с учетом отсутсвия достаточных знаний не знаю. Буду благодарен за подсказку.
 
 
 
#23
03.03.2020 16:39:51
попробуйте так
Код
= if fParam("Region")=null then #TABLE1 else Table.SelectRows(#TABLE1, each [REGIONs]=fParam("Region"))
Вот горшок пустой, он предмет простой...
 
 
 
#24
04.03.2020 07:52:14
PooHkrd,рахмет (спасибо)! Вначале зациклился на функции обработки выражения, когда она вообще не нужна. Работает просто структура if then else.

Премного благодарен.
Изменено: AzatKukanov - 04.03.2020 08:56:46 (Моя ошибка: невнимательно прочитал формулу, предложенную мне. Потом понял свою ошибку. Все работает)
 
 
 
Страницы: 1
Наверх