VBA PowerQuery - создание внутри редактора PQ папок с запросами и помещение в нужную определенный запрос, С помощью VBA в PowerQuery создание внутри редактора PQ папок с запросами и помещение в нужную создаваемые запросы
Часто приходится различные запросы при создании помещать внутри редактора PowerQuery в папки запросов "Источник", "Обработка", "Результат". Запрос из папки "Обработка" ссылается на запрос из папки "Источник", а из папки "Результат" на запрос из папки "Обработка". Папки приходится создавать вручную, а создание запросов создаю вот таким макросом:
Скрытый текст
Код
Sub Add_Connecotion_Current_Table()
Dim wb As Workbook
Dim ws As Worksheet
Dim io As ListObject
Dim tName As String
Dim sName As String
Dim sFormula As String
Dim wq As WorkbookQuery
Dim bExists As VbMsgBoxResult
Dim vbAnswer As VbMsgBoxResult
Dim i As Long
Dim dStart As Double
Dim dTime As Double
'Display message box to prompt user to run the macro
vbAnswer = MsgBox("Run Macro?", vbYesNo, "PowerQuery Connect Current Table")
If vbAnswer = vbYes Then
dStart = Timer
tName = ActiveCell.ListObject.Name
Set wb = ActiveWorkbook
Set io = wb.ActiveSheet.ListObjects(tName)
sName = io.Name
'_____________________________First Query - Источник
sFormula = "Excel.CurrentWorkbook(){[Name=""" & sName & """]}[Content]"
'Check if query exists
bExists = False
For Each wq In wb.Queries
If InStr(1, wq.Formula, sFormula) > 0 Then
bExists = True
MsgBox "Current Name of Query already exists", _
vbOKOnly, "Exit Process"
Exit Sub
End If
Next wq
'Add query if it does not exist
If bExists = False Then
'Add query
wb.Queries.Add Name:=sName & "ИСТ", _
Formula:="let" & Chr(13) & "" & Chr(10) & _
" Source = Excel.CurrentWorkbook(){[Name=""" & sName & """]}[Content]" _
& Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " Source"
End If
'_____________________________Second Query - Обработка
sFormula = sName & "ИСТ"
'Check if query exists
bExists = False
For Each wq In wb.Queries
If InStr(1, wq.Formula, sFormula) > 0 Then
bExists = True
MsgBox "Current Name of Query already exists", _
vbOKOnly, "Exit Process"
Exit Sub
End If
Next wq
'Add query if it does not exist
If bExists = False Then
'Add query
wb.Queries.Add Name:=sName & "ОБР", _
Formula:="let" & Chr(13) & "" & Chr(10) & _
" Source = " & sName & "ИСТ" _
& Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " Source"
End If
'_____________________________Third Query - Результат
sFormula = sName & "ОБР"
'Check if query exists
bExists = False
For Each wq In wb.Queries
If InStr(1, wq.Formula, sFormula) > 0 Then
bExists = True
MsgBox "Current Name of Query already exists", _
vbOKOnly, "Exit Process"
Exit Sub
End If
Next wq
'Add query if it does not exist
If bExists = False Then
'Add query
wb.Queries.Add Name:=sName & "РЕЗ", _
Formula:="let" & Chr(13) & "" & Chr(10) & _
" Source = " & sName & "ОБР" _
& Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " Source"
End If
'Calc run time
dTime = Timer - dStart
MsgBox "Connection have been created in " & Format(dTime, "0,0") & " seconds.", _
vbOKOnly, "Process Complete"
End If
End Sub
Хочется дополнить код, чтобы макрос создавал папки и помещал туда запросы определенным образом. Пытался найти в поиске этого форума и в интернете - не нашел. Как хочу видеть конечный результат - в приложенной картинке.
18. Поле "Комментарий" для названия файла в кнопке "Резервное копирование".
Постоянно пользуюсь кнопкой "Резервное копирование". Хорошо бы в неё добавить возможность подписания к созданному файлу нужной надписи. Иногда хочется как-то выделить конкретную резервную копию, чтобы удобней было между ними ориентироваться. Назвать поле "Комментарий", если туда вписано что-то, то оно добавляется к названию файла справа до расширения файла.
VBA. Userform.ListBox - ошибка вылета Excel и "Out of memory", Урок курса VBA, в котором автор через Userform удаляет записи из умной таблицы, что приводит к вылету всего Excel
VBA. Userform.ListBox - ошибка вылета Excel и "Out of memory", Урок курса VBA, в котором автор через Userform удаляет записи из умной таблицы, что приводит к вылету всего Excel
Скрин показывает, что при заполненных полях, нажимаю "ADD", в умную таблицу успевает добавиться строка с заполненным только первым полем. Ошибка. При нажатии на "Debug", подчеркивается указанная строка кода. При попытке сохранить или закрыть файл, Excel вылетает.
Из-за PQ не использую формат .xlsb, т.к. часто сталкивался с тем, что PQ не может распознать сразу нужные данные. А если распознавал, при следующих обновлениях запроса мог выдать ошибку. Поэтому использую только .xlsx или .xlsm. Также .txt или .csv, если чисто отчеты из 1С, т.к. эти форматы PQ переваривает очень быстро.
VBA. Userform.ListBox - ошибка вылета Excel и "Out of memory", Урок курса VBA, в котором автор через Userform удаляет записи из умной таблицы, что приводит к вылету всего Excel
Сразу технические характеристики системы: i7, SSD, 24 Гб ОЗУ, WIN 10 x64, Office 365 x64, Excel версия 2204 Сборка 16.0.15128.20210.
Обучаюсь курсу VBA, где автор создает через Userform "FmInputMaster" добавление записи в умную таблицу на листе Masterdata, и затем удаление выбранной записи удалением строки из умной таблицы с того же листа через Userform "FmViewMaster" commandbutton "Delete Record".
Уже во время просмотра видео сам автор делает указание, что в конечной версии файла он отказался от этой кнопки и её кода "Delete Record" из-за возникающей ошибки.
И действительно в какой момент вроде всё работает: запись добавляется, удаляется и на какой-то раз удаления (порой уже на 3-4й), возникает ошибка:
Было бы здорово добавить в Plex к функции "Разорвать все связи" или к "Очистка книги" дополнительную возможность удаления всех подключений из вкладки "Данные->Подключения" (Excel 2013), включая модель данных и удаления всех Queries созданых в PowerQuery, да и вообще удаление любых связей в книге с внешними источниками.
Это часто требуется, когда из книги нужно сделать вариант для пересылки и чтобы нигде не оставалось путей и ссылок, откуда взята информация.
Раньше тоже мучился с группировками в 1С, а оказывается их просто в настройках отчета 1С нужно убрать. А те поля что там были выбрать в "Дополнительные поля", размещение - "в отдельнЫХ колонках" и не забыть поставить галку "Выводить детальные записи". Группировок вообще не должно быть!!! Приложил скрин из 1С окна настроек стандартного отчета. Чтобы увидеть закладку "Дополнительные поля", нужно поставить галку "Расширенная настройка".
Также после сохранения такого отчета в формате .xlsx, нужно данный файл открыть в Excel и, ничего не меняя, просто сохранить в самом Excel (лучше 2 раза). Можно даже увидеть, что размер файла изменится. У меня PQ без данной операции не проглатывает 1С-кие отчеты.
let
Source = Excel.CurrentWorkbook(){[Name="Таблица4"]}[Content],
// Группируем по Число и Коду и AllRows по значению
GroupedRows = Table.Group(Source, {"Число", "Код"}, {{"Count", each _, type table}}),
// добавляем внутри таблиц колонки Count Индекс от 0. Он нам понадобиться для создания динамичного листа названия колонок,
// а также для шага PivotedColumn
AddedCustom = Table.AddColumn(GroupedRows, "Custom", each Table.AddIndexColumn([Count], "Index", 0, 1)),
RemovedColumns = Table.RemoveColumns(AddedCustom,{"Count"}),
ExpandedCustom = Table.ExpandTableColumn(RemovedColumns, "Custom", {"Значение", "Index"}, {"Значение", "Index"}),
// Эти 3 строчки кода ниже нам создают динамичный лист названия колонок для объединения данных через запятную для последнего шага MergedColumns.
// Проблема при объединении колонок - это то, что их названия прописываются как говорится Hardcoded. Т.е. если на текущий момент наш запрос сработает,
// то завтра данные обновятся и колонок станет больше, но они в шаг MergedColumns уже бы не попали.
ChangeFutureListToText = Table.TransformColumnTypes(ExpandedCustom,{{"Index", type text}}), // важно чтобы Лист создавался из Text type
CustomIndex = ChangeFutureListToText[Index], // создание листа. Есть кнопка в интерфейсе
List = List.Distinct(CustomIndex), // убираем дубликаты
// Выделяем колонку Index и делаем Pivot по колонке Значение и выбираем в Advance - Dont Aggregate
PivotedColumn = Table.Pivot(Table.TransformColumnTypes(ExpandedCustom, {{"Index", type text}}, "ru-RU"),
List.Distinct(Table.TransformColumnTypes(ExpandedCustom, {{"Index", type text}}, "ru-RU")[Index]), "Index", "Значение"),
// Выделяем колонки от 0 и дальше и соединяем их через ",". По умолчанию (по примеру), там где List в шаге MergedColumns ,
// было бы {"0", "1", "2", "3", "4", "5", "6", "7", "8", "9"}, т.е. жестко прописаны (Hardcoded) названия колонок, а если бы их стало больше,
// то они бы не попали в объединение. Но благодаря выше 3-м шагам мы создали динамичный лист названия колонок
MergedColumns = Table.CombineColumns(PivotedColumn, List ,Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged")
in
MergedColumns
а можно было проще:
Цитата
Максим Зеленский написал: Код ? 1= Table.Group(#"Changed Type", {"Столбец2"}, {{"Concatenated", each Text.Combine(List.Transform(_[Столбец3
Не буду спорить, просто столкнулся на работе с тем, что сотруднику поставили надстройку, у него был Excel 2013 Standard, я же работаю в PRO. Мне нужно было ему показать какое-то действие в этой надстройке. И когда сел показывать увидел, что не все коннекторы были. Все кнопки не проверял, но показалось, что и не все кнопки преобразования были в интерфейсе.
БМВ написал: PowerQuery - доступна и для стандарта, а вот PowerPivot только в про.
Это да, но функционал будет не полный, и на сколько я понял в плане именно интерфейса не все кнопки будут. А вот с точки зрения языка запросов M, ограничений думаю нет.
Возможно пользователям для уже настроенного запроса, где они будут только обновлять хватит и для стандарта. А вот Вам Ings как создателю запросов желательно запускать из Pro.
Как вариант PowerQuery - очень крутая штука и доступна всем. Но нужна Pro версия Excel, чтобы все функции работали. Универсальна для 2010/2013/2016(встроена)/PowerBI(встроена).
https://www.youtube.com/watch?v=8IWrtwMb03s C 1:57 по 5:20 как подключиться к SQL database. На английском, но там всё сопровождается видеорядом, что куда нажимать и вводить.
Тоже на английском, и то же всё наглядно. Как из с листа Ексель можно задать параметры, которые будет обрабатывать PowerQuery при создании запроса к базе данных. Т.е. данный параметр пользователь может задавать сам прямо в книге. Главное, чтобы вы настроили его обработку в PowerQuery.
И кстати если зайти по ссылке от Максима и изучить именно 4-й способ (это где использование команды Record.FromList) можно играться и с 3 и 4 и т.д. повторяющимся группами столбцов. Например может повторяться (2) Дата - Количество, а может (3) Дата – Сумма - ДатаСерт, или (4) Дата – Сумма - ДатаСерт - СуммаРезерв и т.д.
Шаги в статье такие:
1. Promote headers 2. Unpivot al columns except common columns. Now our repeating columns becomes repeating groups of rows. 3. Add “Index” column 4. Transform “Index” column with use of Number.IntegerDivide function. This can be done manually or with UI: Select “Index” column, go to Transform – From Number – Standard – Divide (Integer). Our goal is to get whole number of “Index”/2 (as we have two repeating columns).
Если повторяется 3 названия то “Index”/3, если 4 - “Index”/4 и т.д.
5. Now each pair of row has the same corresponding number. 6. And here is some magic: now we’ll group rows by this modified “Index”, but as an aggregate function we’ll use non-standard Records.FromList function. It is not in list of functions available in dialog window, so it should be entered manually or by editing of other aggregate function code:
2 столбца:
Код
#"Grouped Rows" = Table.Group(#"Integer-Divided Column", {"Имя", "Заказ", "Index"}, {{"Значения", each Record.FromList([Value], {"Дата","Сумма"}), type record}}),
3 столбца:
Код
#"Grouped Rows" = Table.Group(#"Integer-Divided Column", {"Имя", "Заказ", "Index"}, {{"Значения", each Record.FromList([Value], {"Дата","Сумма","ДатаСерт"}), type record}}),
4 столбца:
Код
#"Grouped Rows" = Table.Group(#"Integer-Divided Column", {"Имя", "Заказ", "Index"}, {{"Значения", each Record.FromList([Value], {"Дата","Сумма","ДатаСерт","СуммаРезерв"}), type record}}),
Т.е. просто дописываются названия новых повторяющихся столбцов.
7. Now we can remove “Index” and expand “Values” column by clicking on double arrow right to column name, and we got the table we need! 8. Make sure all data has correct type assigned, close & load.
Создал для себя инструмент планирования остатков и маршрутов в зависимости от наличия остатков на складе на дату, прихода/расхода на дату, с учетом проставления дат по маршрутам по разным товарам по разным заказам. Всё это на основе 3-х таблиц. Но не могу сделать, чтобы я мог проставить на одну дату несколько маршрутов и при этом в таблице учета остатков эти маршруты правильно отображались и суммировали расход на дату. Конечно бы хотелось через стандартные функции это можно было сделать, но если предложите макрос - думаю разберусь.
А может кто-то сбросить ссылку на хорошее описание параметров/значений ".pattern=" ?
Вот например нашел для себя вытаскивание подряд идущих шести любых цифр параметром "\d{6}", это нужно было, когда в середине текста содержался индекс, слева название компании, справа адрес. И нужно было всё это разнести по разным колонкам. Вот вытащив индекс, это стало легко:
Код
[/CODE]Sub ExtNumbers6()
' извлечение нужного количества подряд идущих цифр
Dim c As Range, x
On Error Resume Next
With CreateObject("vbscript.regexp")
.Pattern = "\d{6}" '6 цифр
For Each c In Selection.Cells
c.Value = .Execute(c.Value)(0)
Next
End With
End Sub
[CODE]
ДА! Сработало! =) А если не секрет, что может собой повлечь изменение Like на = ? Т.е. при данном тексте - исправило, а при другом - наоборот? Или функция стала в целом универсальней?
Только обрадовался функции VLOOKUP2, что может подхватить значения, где больше 255 символов, как тут же мне выдал ошибку если в тексте есть открывающаяся квадратная скобка "[". Файл прикрепил.
Выручайте! =) Очень нужна эта функция. Удалить кругом скобки не могу, т.к. работаю с разными отчетами и периодически они обновляются.