Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Курилка
Страницы: 1 2 След.
RSS
Excel 2016 и Power Query
 
#1
01.01.2016 23:10:15
Установил Excel 2016. Решил почитать, чего там нового. Интересное из диаграмм - водопады. И ещё интегрировали Power Query, который раньше был надстройкой (Data => Get & Transform/Данные => Скачать & Преобразовать [ну и перевели!!! ]). Решил посмотреть, что за зверь, так как Andrey VG частенько отвечал в виде Power Query. Прочитал, что у Power Query есть свой язык Power Query Formula Language ("M" Language).
Решил бегло пробежаться и понажимать кнопки. Благо, и повод есть - база в SQL Server'е, в которой 250 000 строк. При подсоединении задал свой простенький SQL:
Код
SELECT * FROM dbo.MyTable
WHERE Id = 0;
Первое, что бросилось в глаза - данные не все были загружены, а точнее - всего лишь 58 строк с сообщением "The data in the preview has been truncated due to size limits". Но где регулировать этот "size limits" - я так и не нашёл.
Я не зря написал SQL-запрос выше. В строке формулы был мой запрос: [Query="SELECT * FROM dbo.MyTable#(lf)WHERE Id = 0;"]. Сначала не понял, где у меня в запросе #(lf), а потом понял, что это знак новой строки (Line Feed).
В общем, думаю почитать про Power Query, но у меня вопрос - насколько необходимо знать язык "M"? Другими словами, какие вещи нельзя сделать в GUI, для которых необходимо знать "M"? И какие есть хорошие книги/статьи? :)
There is no knowledge that is not power
 
 
 
#2
02.01.2016 10:19:44
есть статейка на хабре, там есть и ссылки на неплохие ресурсы по теме.
------
 
 
 
#3
02.01.2016 10:38:21
B.Key, Спасибо, почитаю! :)
There is no knowledge that is not power
 
 
 
#4
02.01.2016 10:49:19
Доброе время суток
Power Query - собственно ещё один язык программирования - функциональный в чистом виде. То есть, хоть Microsoft в документации и упоминает слово Variable, но это по существу объявление определения. После того как  определили название и значение, его уже изменить нельзя (в отличии от процедурных языков) - только породить новое определение или функцию на базе уже существующих. В силу этого явных циклов нет - только рекурсия. Либо циклический перебор списков в рамках встроенных функций: List.Transform, List.Generate, Table.TransformColumns.
Родоначальник использования Power Query на нашем форуме Максим Зеленский Выборка повторяющихся наборов данных.
В рамках использования для решения подобных задач Power Query - мощная штука. В SQL Server для такого решения придётся вводить пользовательский тип данных и обрабоку с использованием .Net Framework. Не силён, но другого  пути не вижу.
А так - основное позиционирование, по моему мнению, преобразование данных, представленных в логической структуре для восприятия человеком, в структуру плоских таблиц для обработки данных. Либо обработка данных из разнородных источников, которые, штатно не поддерживаются SQL. Плюс передать, как плоскую таблицу данных в модель данных Power Query или использовать результат как источник данных для обычной сводной, или использовать результирующую таблицу для дальнейшей обработки формулами Excel.
В общем то, это всё прекрасно делается и на VBA, только вот 100500 раз делать сбор данных из нескольких файлов или писать выборку с использованием словарей уже надоело, а так - освоение нового языка. Есть некоторые преимущества в некоторой универсальности. Но сказать, что что-то можно сделать только в Power Query, по моему мнению, нельзя.
А в чём проблема для вас, что выводится ограниченное число строк в окне предпросмотра? Так поступает, например, Oracle SQL Developer, при желании можно прокрутить до конца. При выводе же результата на лист вы получите все свои 250000 строк.
Изменено: Андрей VG - 02.01.2016 10:55:11
 
 
 
#5
02.01.2016 11:35:08
Цитата
Андрей VG написал:
А в чём проблема для вас, что выводится ограниченное число строк в окне предпросмотра? Так поступает, например, Oracle SQL Developer, при желании можно прокрутить до конца. При выводе же результата на лист вы получите все свои 250000 строк.
Не совсем так, конечно. Раньше в GUI SQL Server'а (если не изменяет память, до 2005 включительно) был зашит вывод ВСЕХ данных в табице, когда делаю выборку через GUI, но позже разработчики изменили вывод - теперь выводятся не все данные, а столько, сколько указано в настройках (по умолчанию - 1 000). Но это не страшно, конечно, так как я всегда могу запросить все данные через T-SQL все строки (SELECT * FROM MyTable), либо сказать мне количество строк (SELECT COUNT(*) FROM MyTable).
Но в PowerQuery вовсе неудобно - он вообще не показывает количество строк в таблице. Вот сейчас взял таблицу с 250 000 строками. PQ говорит мне: 999+ rows. Я фильтрую эти данные (в PQ Editor'е) по условию Итого=0 - опять мне говорит 999+ rows, то есть я опять не знаю, какое количество строк у меня получилось. Это большой минус. То есть чтобы посмотреть отфильтрованные данные (и их количество), мне нужно выгрузить их на лист? А если количество выгружаемых строк превышает количество строк в листе (например, 2 миллиона)? В общем, как-то это не продумано...
Ну а так - посмотрю, поковыряюсь. :) Спасибо за пояснения. :)
Изменено: SuperCat - 02.01.2016 11:36:21
There is no knowledge that is not power
 
 
 
#6
02.01.2016 12:25:00
Цитата
SuperCat написал:
либо сказать мне количество строк (SELECT COUNT(*) FROM MyTable).
Ну, это и в редакторе Power Query не проблема. В окне редактора пусть ваш запрос к базе данных называется Запрос1. Создадим пустой запрос с кодом
Код
let
    Source = Table.RowCount(Запрос1)
in
    Source

И получим информацию о числе строк.
Цитата
SuperCat написал:
А если количество выгружаемых строк превышает количество строк в листе (например, 2 миллиона)?
Вы как человек, занимающийся базами данных, думаю, прекрасно понимаете, что человеку и 1000 строк анализировать на листе сложно. Для этого и существуют сводные, гистограммы и прочие инструменты интегрального представления данных, которые может понять человек. По этому не думаю, что вам нужно выводить 2000000 записей - так ради шутейного спора  :)
Изменено: Андрей VG - 02.01.2016 12:25:53
 
 
 
#7
02.01.2016 12:42:05
Андрей, естественно, что я не буду выгружать миллионы строк - проблема в том, что я не знаю, сколько строк я получил! Сколько выгрузится на лист? Миллион? Два? Две тысячи? 999+ rows - это, извините, бред сивой кобылы.
There is no knowledge that is not power
 
 
 
#8
02.01.2016 13:43:47
Цитата
SuperCat написал:
проблема в том, что я не знаю, сколько строк я получил! Сколько выгрузится на лист?
Похоже я непонятно выразился в предыдущем посте. В примере, Запрос2 возвращает число строк Запроса1.
Полагаю, что Power Query выводит в окне редактора неполное количество строк для того, чтобы не замедлять отладку при разработке запроса. Представьте, что было бы, если бы при каждой проверке, получаете ли вы при изменённых/добавленных строках кода нужный результат, каждый раз выгружались бы миллионы строк.
 
 
 
#9
02.01.2016 14:07:42
Андрей, причина невыгрузки всех данных ясна - оптимизация (как я раньше писал про SQL Server - ограничение кол-ва выгружаемых строк). Здесь понятно. Но! PQ говорит мне "999+ rows" - значит, PQ знает, сколько всего строк! Так понятнее? :) То есть мне как бы выгружать всё не надо, а хотя бы показать количество строк - просто количество. А в идеале решение выгружить или не выгружать ВСЕ строки - должен принимать пользователь, а не разработчик. Представьте то же самое в SQL Server'е - вы говорите ему SELECT *, а он вам выгрузит 28 строк и скажет привет! И да - итоговое количество строк надо показать, а не мистическое "999+ rows".
There is no knowledge that is not power
 
 
 
#10
30.08.2017 10:02:14
Андрей VG, а можно сделать так, чтобы запрос2 возвращал кол-во строк не только запроса1, но запроса3, 4, 5и тд, если они есть? Очень неудобно не видеть результата, когда работаешь со многими запросами, удаляешь дубли, фильтруешь несколько баз. Можно ли сделать запрос, который бы собирал информацию из других запросов в виде таблицы?
 
 
 
#11
30.08.2017 11:57:17
Доброе время суток.
Цитата
Shima написал:
чтобы запрос2 возвращал кол-во строк не только запроса1, но запроса3, 4, 5и тд, если они есть?
А вы уверены, что в Power Query результаты всех запросов только - таблицы? Плюс, чтобы получить число строк запроса, возвращающего таблицу, нужно чтобы этот запрос выполнился - может быть долго :)
Код
let
    source = Record.ToTable(#shared),
    onlyTable = Table.SelectRows(source, each Value.Is([Value], Table.Type)),
    rowCount = Table.AddColumn(onlyTable, "Row Count", each Table.RowCount([Value]), Int64.Type)
in
    rowCount

Успехов.
 
 
 
#12
30.08.2017 13:06:06
Добрый день. Спасибо, то что нужно получилось.
Цитата
Андрей VG написал:
А вы уверены, что в Power Query результаты всех запросов только - таблицы?
Нет конечно. Я до этого момента вашим примером Source = Table.RowCount(Запрос1) пользовался. Создавал под каждый запрос или просто создавал запрос - считать строки. Вот и подумал, что скорее всего как-то можно собрать все данные в одну таблицу. Попытался сам сделать, но ничего не вышло. Спасибо.
 
 
 
#13
30.08.2017 13:12:51
Цитата
Андрей VG написал:
Table.AddColumn(onlyTable
тут рекурсия частенько возникает. Я бы выводил в запись, чтобы не пыталась саму себя посчитать.
F1 творит чудеса
 
 
 
#14
30.08.2017 17:22:09
Цитата
Максим Зеленский написал:
тут рекурсия частенько возникает
Максим, у меня без неё обошлось в 2010. Можно в конце концов и фильтр на искючение имени добавить.
 
 
 
#15
30.08.2017 20:02:32
Андрей VG, Если не лезть в саму таблицу, то да, нет циклической ссылки, насколько могу судить.
Нашел, кстати, любопытный артефакт: попробовал вывести на лист, сначала вывело как надо, а вот при обновлении - увы. Такое ощущение, что, так как связей между таблицами нет, во время оценки этой счетной таблицы она в Section1 - единственная.

но если добавить правильную ссылку в код, то выведутся обе.
Код
let
    source = Record.ToTable(#sections[Section1]),
    onlyTable = Table.SelectRows(source, each [Value] is table),
    rowCount = Table.TransformColumns(onlyTable, {"Value", Table.RowCount}),
    test = if Table.IsEmpty(Таблица1) then #table({},{}) else rowCount 
in
    test
F1 творит чудеса
 
 
 
#16
31.08.2017 05:27:23
У меня все выводит в таблицу.

Код
//у меня так работает
let
    source = Record.ToTable(#sections[Section1]),
    onlyTable = Table.SelectRows(source, each [Value] is table),
    rowCount = Table.TransformColumns(onlyTable, {"Value", Table.RowCount})
in
    rowCount


/* 
так не работает, пишет что Expression.Error: Имя "Таблица1" не распознано. Убедитесь в том, что оно написано верно.
let
    source = Record.ToTable(#sections[Section1]),
    onlyTable = Table.SelectRows(source, each [Value] is table),
    rowCount = Table.TransformColumns(onlyTable, {"Value", Table.RowCount}),
    test = if Table.IsEmpty(Таблица1) then #table({},{}) else rowCount 
in
    test 
*/
Изменено: Shima - 31.08.2017 05:28:10
 
 
 
#17
31.08.2017 10:31:02
Цитата
Shima написал:
У меня все выводит в таблицу.
у меня тоже вывело, один раз. второй раз не хотело.
Цитата
Shima написал:
так не работает, пишет что Expression.Error: Имя "Таблица1" не распознано.
правильно пишет. у вас же нет в книге запроса с именем "Таблица1"
F1 творит чудеса
 
 
 
#18
31.08.2017 16:50:04
Максим, спасибо за варианты. Действительно, мой на лист выводится только раз, хотя в просмотре Power Query в самом редакторе показывает всё как есть. Чудны дела PQL.
 
 
 
#19
01.09.2017 11:11:33
Андрей VG, Shima,
а вот, кстати, и объяснение подоспело. Логично с точки зрения "ленивости", но вызывает сомнение в пригодности использования #shared или #sections как универсального доступа к любым кастомным объектам.
В свое время хотел сбацать функцию, которая собирает календарь из всех доступных таблиц по столбцам даты, но наткнулся на ту же историю и бросил.
F1 творит чудеса
 
 
 
#20
01.09.2017 14:58:34
Цитата
Максим Зеленский написал:
как универсального доступа к любым кастомным объектам
Да, пожалуй.
Можно разве что в сводке по числу строк таблиц запросов вести аккуратный список всех запросов, за которыми нужно следить? тогда это всё работает.
tableQueris = {Query1, sqlQuery2, Таблица1}. Не думаю, что такой список уж очень сложно будет поддерживать, достаточно
 
 
 
#21
01.09.2017 17:57:56
Так а что не так? Все же работает, оба варианта в самом Power Query показывают кол-во строк.  А то что не выводит на лист, так оно по большому счету и не нужно. В самом ведь файле, если данные загружены в модель данных, видно и запросы и кол-во строк. Да и не трудно мышкой навести на запрос. Неудобен модальный режим Power Query, пока ты в нем, то ничего посмотреть в Excel не можешь. Я вот пока другого варианта не нашел, как "пробрасывать" туда все что нужно через лист Parameters. Может я чего не понимаю.

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

Не разобрался я почему он в одном случае распознает месяц и год, а во втором нет. Гипотеза такая, когда загружаешь папки через интерфейс PQ, то он распознает, а если через формулы то нет. Короче просто добавил поля FORMAT([Дата];"YYYY") FORMAT([Дата];"MMM")
Изменено: Shima - 02.09.2017 09:47:07
 
 
 
#22
05.09.2017 19:00:35
Цитата
Shima написал:
Но у меня до сегодняшнего дня, все было нормально.
в обычных сводных все нормально группируется.
в Power Pivot группировки задаются вручную, но для них нужны дополнительные поля.
F1 творит чудеса
 
 
 
#23
06.09.2017 03:54:18
Максим Зеленский, да я это понял. Не понятно почему в разных случаях, когда строишь сводную из Power Pivot, то в одном случае  Excel сам добавляет поля дата-месяц и дата-год, а в другом их приходится создавать. И во втором варианте, в  срезе сортировка месяцев по алфавиту, а не по порядку.
Еще вопрос по PP. Встретил ролик, где вы рекомендуете поставить Power Pivot Utilities. Попробовал поставить, не получается. Вроде все делаю, как написано но он не видит файл с расширением .xlam
Скрытый текст
 
 
 
#24
06.09.2017 16:36:04
Попробуйте положить его не в отдельную папку внутри AddIns, а просто в AddIns.
Цитата
Shima написал:
Не понятно почему в разных случаях, когда строишь сводную из Power Pivot, то в одном случае  Excel сам добавляет поля дата-месяц и дата-год, а в другом их приходится создавать.
Действительно, в некоторых случаях создает иерархии дат сам. Честно говоря, не помню, причину обсуждали, но не могу сказать точно.
Цитата
Shima написал:
И во втором варианте, в  срезе сортировка месяцев по алфавиту, а не по порядку.
Потому что это вручную созданный текстовый столбец, и для PP не очевидно, что он на самом деле месяц. Нужно создать доп.столбец =MONTH([Date]) и в окне Power Pivot найти кнопку "Сортировка по столбцам" ("Sort by column") - задать сортировку столбца "имя месяца" по столбцу "номер месяца"
F1 творит чудеса
 
 
 
#25
07.09.2017 04:32:42
Спасибо, с сортировкой понял.
С PP_utilities такая история. Действительно она загрузилась, когда положил ее просто в AddIns. Но при закрытии и открытии Excel заново, она не появляется на ленте. Хотя и находится в активных надстройках. Опять указываю путь и она появляется. У меня Excel 2013. Также на вижу на ленте во вставке Power View.
 
 
 
#26
07.09.2017 13:27:34
есть такой глючок в ней, увы. периодически приходится ее взбадривать (особенно в 2016-м)
точнее даже так, помогает переустановка  галочки в окне Надстройки на вкладке Разработчик. Сначала снять, ОК, потом поставить, ОК
Изменено: Максим Зеленский - 07.09.2017 13:28:23
F1 творит чудеса
 
 
 
#27
07.09.2017 15:20:42
Максим Зеленский, спасибо. А то я думал, может я где-то косячу. Все в новинку
 
 
 
#28
20.09.2017 16:36:42
Добрый день!
Подскажите, пожалуйста, можно ли загрузить в редактор запросов таблицу без скрытых строк и столбцов? При загрузке отображает все скрытые строки и столбцы..
 
 
 
#29
20.09.2017 17:59:12
Цитата
Владимир Ш. написал:
можно ли загрузить в редактор запросов таблицу без скрытых строк и столбцов?
Если создать нечто дополнительное, что будет указывать, какие строки/столбцы использовать (ну, или наоборот). В прямую не предусмотрено, но можете пойти путём Максима Зеленского - прямым анализом содержимого xml узлов и атрибутов листа с таблицей (только в случае файлов xlsx, xlsm).
 
 
 
#30
20.09.2017 18:24:37
Если речь идет об одном файле, то проще как сказал Андрей VG, пометить строки перед загрузкой (например, при помощи функции АГРЕГАТ). Если файлов много и нет возможности/желания проверять каждый раз, то в можно слегка подредактировать немного другую функцию, которая получает иерархию строк, чтобы она получала вместо этого состояние видимости строки.
F1 творит чудеса
 
 
 
Страницы: 1 2 След.
Наверх