Выбрать дату в календареВыбрать дату в календаре

Страницы: 1 2 3 4 5 След.
Разворачивание нескольких полей вложенной таблицы PQ
 
Сработала функция (выкладываю, может, кому-то пригодится).
Код
let
    MergeNestedTableFields = (table as table, nestedTableField as text, fieldNames as list, optional delimiter as nullable text) as table =>
    let
        // Если delimiter не передан, использовать разрыв строки
        delimiterValue = if delimiter = null then "#(lf)" else delimiter,
        
        // Функция для приведения поля к тексту, если оно не является текстом
        ConvertToText = (value) =>
            if Value.Is(value, type text) then value else Text.From(value),
        
        // Добавление объединенных полей
        AddMergedColumns = List.Accumulate(fieldNames, table, (currentTable, fieldName) =>
            Table.AddColumn(
                currentTable,
                fieldName,
                each Text.Combine(List.Transform(Table.Column(Record.Field(_, nestedTableField), fieldName), ConvertToText), delimiterValue),
                type text
            )
        )
    in
        AddMergedColumns
in
    MergeNestedTableFields
Разворачивание нескольких полей вложенной таблицы PQ
 
Здравствуйте! При использовании PQ регулярно встает задача развернуть вложенную таблицу. Когда нужно вытащить одно поле - вопросов нет. А когда их много, приходится дублировать шаги, что иногда приводит к путанице и всегда к увеличению времени выполнения запроса. Задал вопрос ИИ по составлению функции, которая на входе примет имя поля с вложенной таблицей и список полей этой вложенной таблицы, которые нужно развернуть, создать одноименные поля в результирующей таблице и в этих полях объединить значения через указанный разделитель. Получил такой код функции:
Код
let
    // Определяем функцию
    CombineNestedFields = (InputTable as table, NestedColumnName as text, FieldNames as list) as table =>
    let
        // Разворачиваем вложенную таблицу
        ExpandedTable = Table.ExpandTableColumn(InputTable, NestedColumnName, FieldNames),
        
        // Объединяем строки из каждого указанного поля
        CombinedColumns = List.Transform(FieldNames, (fieldName) =>
            Table.AddColumn(ExpandedTable, "Combined_" & fieldName, each Text.Combine(Record.FieldValues(_, fieldName), ", "), type text)
        ),
        
        // Убираем дубликаты и оставляем только нужные столбцы
        FinalTable = List.Accumulate(CombinedColumns, InputTable, (state, current) => Table.RemoveColumns(state, FieldNames))
    in
        FinalTable
in
    CombineNestedFields
Код применения функции
Код
let
    Source = ... , // ваш источник данных
    Result = CombineNestedFields(Source, "Табл", {"Поле1", "Поле2"}) // замените на имя вашего столбца с вложенной таблицей и нужные поля
in
    Result

Но при использовании возникает ошибка, что "Поле1" не существует.
Прошу помощи в корректировке функции и ее применения. Спасибо.
Сохранение функций PQ
 
Здравствуйте! Подскажите, пожалуйста, возможно ли сохранить пользовательские функции PQ аналогично пользовательским функциям Excel в надстройку, чтобы можно было ими пользоваться в любом файле? И если можно, то как это сделать? Спасибо.
Регулярное выражение для замены подстроки и смены регистра
 
Kuzmich, на просторах инета нашел код для UDF
Код
Public Function ReplaceReg(text As String, pattern As String, text_replace As String, Optional instance_num As Integer = 0, Optional match_case As Boolean = True, Optional registr As Boolean = True) As String    Dim text_result, text_find As String, regex As Object, matches As Variant
    Dim matches_index, pos_start As Integer
     On Error GoTo ErrHandle
    text_result = text
    Set regex = CreateObject("VBScript.RegExp")
     regex.pattern = pattern
    regex.Global = True
    regex.MultiLine = True
     If True = match_case Then
        regex.IgnoreCase = False
    Else
        regex.IgnoreCase = True
    End If
     Set matches = regex.Execute(text)
     If 0 < matches.Count Then
        If (0 = instance_num) Then
            text_result = regex.Replace(text, text_replace)
        Else
            If instance_num <= matches.Count Then
                pos_start = 1
                For matches_index = 0 To instance_num - 2
                    pos_start = InStr(pos_start, text, matches.Item(matches_index), vbBinaryCompare) + Len(matches.Item(matches_index))
                Next matches_index
                 text_find = matches.Item(instance_num - 1)
              If registr = True Then
                text_result = Left(text, pos_start - 1) & Replace(text, text_find, LCase(text_replace), pos_start, 1, vbBinaryCompare)
              Else
                text_result = Left(text, pos_start - 1) & Replace(text, text_find, text_replace, pos_start, 1, vbBinaryCompare)
              End If
            End If
        End If
    End If
     ReplaceReg = text_result
    Exit Function
 ErrHandle:
    ReplaceReg = CVErr(xlErrValue)
End Function

Как его можно скорректировать с учетом Вашего предложения? Я не смог ((
Регулярное выражение для замены подстроки и смены регистра
 
Добрый день.
Использую регулярку \d+)([а-яА-Я]) для поиска подстроки с заменой $1 $2 для исправления адресов, в которых литера дома "слипается" с номером (типа "дом 5а" или "5А". То есть просто вставляю пробел. Но хотелось бы привести литеру к нижнему регистру, чтобы в обоих случаях получалось "дом 5 а". И никак не получается сменить регистр. Прошу помочь.
Спасибо!
В PowerQuery отображаются не все запросы БД
 
Добрый день, гуру!
Если вопрос не по адресу, прошу модераторов простить.
Ситуация такая: из БД Access данные выгружаются в Excel через PQ. Раньше сталкивался с тем, что долго обрабатываются в PQ, поэтому максимум обработки задаю в БД, формируя там запросы, а уже их и выгружаю.
Но почему-то не все запросы, сделанные в БД, отображаются в PQ и, следовательно, не выгружаются в Excel. На картинках списки запросов в БД и PQ, выделены запросы, которые мне нужны для выгрузки.
Я предположил, что запросы на объединение не выгружаются, а также запросы с пользовательскими функциями (в приложенном документе текст запроса на выборку с включением пользовательской функции - своего рода "множественный ВПР"). Использование этой функции в Excel или через объединение таблиц в PQ сильно тормозит работу.
Собственно, вопрос: я прав, что пользовательские функции и запросы "не на выборку" не могут выгружаться? Если это так, есть ли способ обойти это ограничение?
Спасибо!
Plex что бы хотелось ещё, Тема просто как фиксатор идей для такого замечатального инмтрумента как Plex , ничему не обязывая Николая,
 
В копилку "хотелок": подсчет количества уникальных значений в отфильтрованных (видимых) строках (что-то типа Промежуточных Итогов).
Склонение сложных должностей (или поиск и замена части текста в ячейке текстом из другой ячейки)
 
Описание решения очень похожей задачи есть в Приемах. Я, например, воспользовавшись этим советом, склоняю названия юридических лиц.
Изменено: kalle - 28.06.2023 11:10:32
Подсчет количества уникальных значений из нескольких диапазонов по условию
 
И еще: можно ли учесть возможность отсутствия одного из условий (например, выбирать только по датам, не учитывая имя)?
Подсчет количества уникальных значений из нескольких диапазонов по условию
 
Цитата
написал:
Это усложняет формулу
Хорошо. Тогда подскажите, пожалуйста, учитывая, что в реальном файле таблицы на листах - выгрузки из PQ - если я добавлю столбец с формулой в каждую из таблиц, не возникнут проблемы с запросами и будет ли пересчитываться результат?
Подсчет количества уникальных значений из нескольких диапазонов по условию
 
МатросНаЗебре, спасибо! Я правильно понимаю, что на каждом листе нужно сделать дополнительные столбцы? Ваш первый вариант (без доп.столбцов), к сожалению, не сработал - после изменения исходных данных подсчет был неправильный. Но принцип без допстолбцов мне нравится больше.
Подсчет количества уникальных значений из нескольких диапазонов по условию
 
Добрый день, уважаемые мастера! Прошу помочь.
Используется Excel2016.
Имеется несколько таблиц на разных листах ("умные таблицы") - это выгрузки, сделанные из PQ. Значения могут дублироваться как в рамках одной таблицы, так и в разных. Стоит задача подсчитать количество уникальных значений, учитывая условия, указанные на листе "Результат". То есть, если в столбце "Дата" любой таблицы значение попадает в указанный диапазон И в столбце "Имена" значение равно указанному, значение из столбца "Данные1(2,3)" нужно учитывать при подсчете.
Пока эту задачу я решал так: фильтровал каждую таблицу поочередно, копировал отфильтрованные данные на новый лист и удалял дубликаты. Естественно, очень непродуктивно, в том числе потому что при изменении условий фильтра все приходится повторять. Надеюсь понятно изложил ))
Желательно использовать формулы, хотя возможно и PQ.
Заранее спасибо откликнувшимся!
Импорт данных из папки с несколькими файлами (Power Query)
 
У меня была такая же ерунда. Установил, что один из файлов содержал ошибку. Связана ошибка была со скрытием столбцов. Когда ошибку устранил, все загрузилось нормально.
Макрос проверки контрагентов через сервис ФНС
 
Цитата
написал:
делал как-то себе такое, запросом PQ, не макрос
ЗдОрово! Спасибо! Скажите, пожалуйста, что нужно дописать в функции, чтобы, кроме статуса и названия в соседние столбцы подтягивались адрес и дата прекращения деятельности (если такая есть)?
Plex что бы хотелось ещё, Тема просто как фиксатор идей для такого замечатального инмтрумента как Plex , ничему не обязывая Николая,
 
12. В инструменте "Объединить" добавить возможность объединения (сцепки) текстов из разных ячеек, но не в ОБЪЕДИНЕННУЮ ячейку, а в единичную. Может пригодиться тем, кто часто разгребает сложные шапки таблиц. И не только.
Ошибка в PQ "Expression.Error: Индекс находится за пределами границ записи", При загрузке отдельных таблиц из БД в PQ возникает ошибка
 
Методом проб и ошибок удалось выяснить, что ошибки возникают при импорте таблиц, в которых есть подстановки из других таблиц с возможностью множественных значений (например, в таблице Лицензий есть указание на несколько записей из справочной таблицы лицензированных программ). Но это, увы, не приводит к пониманию пути решения задачи.
Хелп!
Ошибка в PQ "Expression.Error: Индекс находится за пределами границ записи", При загрузке отдельных таблиц из БД в PQ возникает ошибка
 
Добрый день!
Из БД в акссесс в excel-файл должны выгружаться через PQ отдельные данные. Часть таблиц выгружается нормально, но большинство выдает ошибку Expression.Error: Индекс находится за пределами границ записи (пример ошибки на скрине, код запроса ниже).

Поиск такой ошибки в инете дал только совет: проверить имена полей в таблице БД. Но имена полей нормальные, никаких спецсимволов нет.
В чем может быть причина такой ошибки?
Заранее благодарен.
Код
let
    Источник = Access.Database(File.Contents("C:\Users\a.kontar\Desktop\Прототип БД лицензий.accdb"), [CreateNavigationProperties=true]),
    _Лицензии = Источник{[Schema="",Item="Лицензии"]}[Data]
in
    _Лицензии
Разнести составные почтового адреса по столбцам
 
В аналогичной ситуации я ориентировался в качестве разделителя на "запятая+пробел" перед заглавной буквой. Большинство случаев разбивает. На этом форуме мне помогли сделать UDF, которая "переворачивает" адрес.
АвтоФильтр не фильтрует ведущие и хвостовые пробелы, Autofilter can't Filter the Srings with spaces in the begining or/and in the end of the text
 
Вчера только столкнулся с подобной ситуацией: в ячейке ТОЛЬКО пробел. Фильтр по столбцу показывает пустое значение (вместе с ячейками, в которых нет НИЧЕГО). А PowerQuеry посчитал ячейку непустой. Долго искал  :evil:  причину, почему exel и PQ дают разные значения количества пустых ячеек.
Исправление UDF (склонение по падежам), UDF работает некорректно
 
, спасибо!
Исправление UDF (склонение по падежам), UDF работает некорректно
 
Спасибо огромное, Дмитрий! Честно говоря, в первом варианте у меня и было обращение к TwisWorkbook, а потом я решил, что если функция применяется в другом файле, то именно к этому файлу ThisWorkbook будет относиться и что нужно прямо указать имя надстройки.
С готовностью и благодарностью приму советы по оптимизации кода.
Изменено: kalle - 09.11.2021 09:15:27
Исправление UDF (склонение по падежам), UDF работает некорректно
 
Всем добрый день. О склонении по падежам уже немало поднято тем, но применить для своей задачи удалось немного. Передо мной стоит задача склонять по падежам названия организаций. Я сделал функцию, вложил ее в надстройку. Логика работы функции такая: название (например, муниципальное учреждение "Школа №15" делится на части по первой кавычке: муниципальное учреждение - и "Школа №15"), затем первая часть разбивается по словам, каждое слово ищется в словаре и по принципу ВПР заменяется на соответствующее. Если кавычки нет, то берется все название целиком. Затем выбранные слова сцепляются между собой и потом со второй частью, находящейся в кавычках.
Словарь находится в файле надстройки на листе "Справочник".
Проблема в том, что если в файле, в котором я применяю функцию, нет листа "Справочник", функция не срабатывает.
Прошу помочь решить эту проблему. Также буду признателен за любые предложения по оптимизации кода функции. В том числе, мне кажется, лучше если словарь будет загружаться в память однократно, а то сейчас по логике функции он подгружается при каждом ее использовании.
Собрать уникальные значения в одну ячейку макросом
 
Цитата
МатросНаЗебре написал:
Начальные ячейки диапазонов задаются пользователем
В рабочем файле данные собираются в 13 столбец ("М"). Я не могу понять, где нужно изменить адрес столбца-назначения.
Собрать уникальные значения в одну ячейку макросом
 
Спасибо, буду разбираться ;)
Подсчет уникальных значений в столбце по заданному фильтру и вывод значений в отдельную ячейку
 
Добрый день. Передо мной стояла практически такая же задача. Макрос от МатросаНаЗебре помог, но адаптировать под себя я не смог. А вот в книге Н.Павлова по PowerQuery на страницах 88-90 пошагово описано решение. Посмотрите, может, пригодиться.
Использованный макрос сможете найти в моем вопросе 1-2 недели назад.
Собрать уникальные значения в одну ячейку макросом
 
Уважаемые форумчане!
Возобновляю тему, потому что задача, заявленная в начальном посте, становится регулярной. Но, к сожалению, не получается адаптировать макрос на листах с похожими данными, но на которых идентификаторы объектов и группы событий находятся в иных столбцах.
В этой связи прошу "допилить" макрос, учитывая:
1. искать группы нужно по идентификаторам, а не названиям объектов, потому что идентификаторы уникальны, а названия объектов могут совпадать.
2. предоставить пользователю возможность через inputbox указывать столбцы (или начальные ячейки диапазонов), в которых находятся идентификаторы на листе объектов и группы на листе событий.
Спасибо всем, кто откликнется!
Собрать уникальные значения в одну ячейку макросом
 
МатросНаЗебре, макрос сработал на файле-приеме, спасибо. Правда, не уверен, что на рабочем файле разрешат запускать макросы, но попробую завтра.
Можно попросить комментарии к коду, чтобы адаптировать его к реальности?

А формулами никак не получится?
Собрать уникальные значения в одну ячейку макросом
 
Mershik, ВПР я использовал на реальном файле. Сейчас из дома сделал "модель" (что есть и что нужно) и обращаюсь за помощью.
Я делал во вспомогательных столбцах, вытаскивая из таблицы событий группы, а потом пытался их сцеплять. Выбрать уникальные не удалось от слова "совсем", а вот с вытаскиванием групп в случае единичных вхождений - с этим я столкнулся: в таблице событий есть, а в объекты не попала.
Собрать уникальные значения в одну ячейку макросом
 
Добрый день, уважаемые форумчане!
Что есть: таблица объектов с уникальными идентификаторами, таблица событий, случившихся с этими объектами. События объединены в группы. Общее число групп известно - их около 40. С каждым объектом могут происходить события как из одной группы, так и из разных, а могут не происходить вовсе. Таблицы разнесены по разным листам.
Передо мной стоит задача: в таблице объектов в одной ячейке перечислить ВСЕ группы событий, случившихся с ним. При этом если с одним объектом случаются несколько событий одной группы, то группа должна указываться только один раз. Перечень групп в этой ячейке должен быть разделен переводом строки (Alt-Enter).
Я пробовал использовать множественный ВПР из Приемов на этом сайте и функции из Plex, но качественного результата не получил. В частности, если у объекта только одно событие (естественно, только одна группа), то значение не подгрузилось.
Используется Excel16.
Пример, сделанный вручную, "как хочется, чтобы было", приложен.
Очень надеюсь на помощь гуру. Заранее благодарю.
Изменено: vikttur - 06.07.2021 12:52:49
Подключение в Excel к Access - некорректно отображаются данные при получении данных
 
Совсем ничего не понимаю: действительно в скачанном файле пусто. Хотя в списке подключений имеется запись "Связь с Access". Прикладываю снова файл, в котором только что наличие подключения проверил.
Подключение делал через меню Данные - Из Access - указывал путь и имя БД, имя таблицы, все остальное по умолчанию. PQ на компьютере нет.
Страницы: 1 2 3 4 5 След.
Наверх