Поиск  Пользователи  Правила 
Закрыть
Логин:
Пароль:
Забыли свой пароль?
Регистрация
Войти
 
Выбрать дату в календареВыбрать дату в календаре

Страницы: 1 2 След.
Экспорт данных из Power Query в БД PostgreSQL
 
Добрый день! Коллеги, прошу вашу помощь с Power Query. Требуется экспортировать данные, полученные в ходе запроса PQ в базу данных PostgreSQL.
В файле примере есть запрос PQ_price это данные которые необходимо экспортировать в БД и запрос PSQL_price это таблица в БД в которую нужно импортировать данные из запроса PQ_price.
Гугл говорит, что в PQ существуют функции для работы с БД, для которых можно передать свой SQL запрос в качестве аргумента. Например, PostgreSQL.Database и Value.NativeQuery. Но не смог найти использование этих функций именно для экспорта данных. Подскажите возможно ли это?
Изменено: Murderface_ - 15 июл 2020 14:11:15 (Файл с решением)
Power Query объединение 2х таблиц по столбцу, отсутствующему в одной из таблиц
 
Добрый день! Прошу подсказать как решить следующую задачу. Есть 2 таблицы
all - все поставщики
Supplier2 - только Поставщик 2.
Требуется объединить эти таблицы по дате и поставщику, но в таблице Supplier2 отсутствует столбец "Поставщик". Если его создавать, то в каждой строке будет одно и тоже - "Поставщик 2".
Возможно ли произвести объединение, не создавая дополнительных столбцов в таблице Supplier2, а например просто указав чему должен быть равен ключ внутри функции Table.NestedJoin?
Преобразование многоуровневой таблицы в плоскую с помощью Power Query
 
Добрый день! Коллеги, нужна ваша помощь в решении задачи на PQ. Необходимо преобразовать многоуровневую таблицу в плоскую. Сама таблица расположена в файле-примере на листе "Данные". На листе "Эталон" желаемый результат. Таким образом каждый столбец в исходных данных, начиная с 3го будет разделен на 3 столбца. В запросе "1 столбец" я добился желаемого результата, но только для 1 столбца.

В теории придумал такое решение: можно на основе этого запроса сделать пользовательскую функцию, которую в дальнейшем можно применить к каждому столбцу начиная с 3. Затем объединить полученные с помощью функции таблицы с помощью Table.Combine. Все это должно выполняться внутри Table.Group например, чтобы при разворачивании таблиц остались 2 первых столбца из исходной. Ну это все теория, а как подобное сделать на практике я не совсем понимаю. Возможно есть более правильное решение.
Power Query создание дополнительного с признаком B2B/B2C при объединении нескольких таблиц
 
Добрый день! Коллеги, нужна ваша помощь в решении задачи на PQ. В файле примере есть запрос all в котором на шаге addB2 определена принадлежность к B2B или к B2C, в данном случае на основе имени листа. На следующем шаге с помощью Table.Combine все данные собираются в одну таблицу. Подскажите пожалуйста как добавить в итоговую таблицу столбец с B2B или B2C, который был получен на предыдущем шаге?

Можно конечно развернуть таблицу из столбца fn c помощью Table.ExpandTableColumn и затем удалить ненужные столбцы, но уверен, что есть более красивое решение.
Относительные ссылки в условном форматировании для набора значков
 
Добрый день! Столкнулся с проблемой при настройке УФ для значков. Есть 2 таблицы, одна с фактическими значениями, другая с плановыми. На основе плановых значений необходимо проставить значок в виде зеленой галочки если план выполнен, если нет то красный крестик. При попытке использовать относительные ссылки в настройках УФ, Excel ругается. Решил схитрить использовав формулу, вместо ссылки на ячейку с целью, но все равно не сработало. Подскажите пожалуйста как можно решить данную задачу?
PQ замена разделителя, для преобразования текстовых значений в числовые
 
Коллеги, добрый день! Прошу помочь в решении очередной задачи на Power Query. Стоит задача собирать данные из нескольких файлов, в которых есть ошибки. В числах в качестве разделителя вместо запятой иногда стоят точки, поэтому значение становится текстовым. При попытке заменить все точки на запятые с помощью Table.ReplaceValue чудо не происходит. Данную замену необходимо произвести во всех столбцах начиная с 3. Подскажите как правильно это сделать?
Консолидация данных в PQ с помощью справочника с адресами файлов и названиями листов
 
Коллеги, добрый день! Возникла необходимость собирать данные с разных листов из разных файлов, хаотично разбросанных в сети. Ничего лучше не придумал, чем создать справочник с адресом к каждому файлу и список листов, которые нужны, благо ни имя файла ни его адрес не изменится. Данное решение будет работать на PQ. У меня возникла проблема с листами. Если файл 1 и лист 1 то все ок, но если файл один а листов несколько, то я не знаю как правильно передать имена листов для PQ, чтобы получить таблицу с этого листа. Можно конечно для каждого листа в таблице справочнике создавать отдельную строку, где адрес будет многократно повторяться для каждого листа, но ИМХО получится колхоз. Может быть вообще можно как то иначе все сделать. Подскажите пожалуйста.
Создание массива данных из 2х столбцов разных таблиц внутри формулы
 
Коллеги, добрый день! Эта тема является продолжением другой темы, но задача тут другая. В файле примере есть лист "Две таблицы", на котором содержатся те же самые данные, что и на листе "Одна таблица", только они разделены на 2 таблицы. В столбцах с оранжевой заливкой необходимо прописать формулы, аналогичные формулам с листа "Одна таблица", но чтобы они учитывали данные сразу из 2х таблиц "Тест1" и "Тест2". Таким образом полученный результат для каждой из таблиц должен совпадать с общим результатом с листа "Одна таблица".

Главная сложность заключается в том, чтобы получить массив данных из 2х таблиц внутри формулы. Надеюсь, что все понятно объяснил.
Присвоение группы для списка значений, пропорционально их доли их количества в списке
 
Добрый день! Есть задача, простая на первый взгляд, но как решить не знаю. Есть список значений, который необходимо распределить на 3 группы:
Группа А = первые 15% значений
Группа Б = следующие 35% значений
Группа С = оставшиеся 50% значений
Важно! % считается не от суммы значений а от их количества.

Есть следующие сложности:
1) В списке есть нулевые значения, которые нужно исключить при присвоении группы.
2) Сортировка не должна влиять на итоговый результат.
3) Пороги должны быть динамическими. Например в файле-примере 149 не нулевых значений. Группа А = 15% * 149 = 22,35, но мы должны присвоить группу 26 значениям, т. к. 22 значение = 26 значению = 90.
Изменено: Murderface_ - 27 мар 2020 17:13:27 (добавил решения)
Поиск сотрудников с не пересекающимися сменами
 
Коллеги, добрый день! Прошу помощи в решении задачи. Необходимо распределить сотрудников по парам, чтобы их смены не пересекались между собой. Пример во вложении.
Рассчитать сумму вклада VBA
 
Добрый день! Коллеги, прошу помощи в решении задачи на VBA. Есть сумма вклада, которая каждый месяц увеличивается на определенный процент. Каждый следующий месяц процент начисляется не на первоначальную сумму вклада, а на сумму предыдущего месяца. Как с помощью VBA получить итоговую сумму вклада?
Создание сводной диаграммы с помощью VBA
 
Добрый день! Прошу помощи в создании сводной диаграммы средствами VBA. В примере есть 2 сводных таблицы на разных листах. С помощью макрорекордера получил вот такой код:
Код
Sub Макрос1()
'
' Макрос1 Макрос
'

'
    Range("A6").Select
    ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
    ActiveChart.SetSourceData Source:=Range("'сводная 1'!$A$3:$B$6")
End Sub
я просто ткнул в случайное место сводной таблицы на листе "сводная 1" и выбрал сводную диаграмму. Подскажите пожалуйста можно ли сделать так, чтобы при запуске макроса он запрашивал кликнуть в любое место сводной таблицы и уже после строил сводную диаграмму? При этом сводные таблицы могут быть расположены на разных листах. Предполагаю, что нужно задать переменную, вместо Range("'сводная 1'!$A$3:$B$6"), но я полный ноль в VBA.
VBA копирование ячейки с условием
 
Добрый день! Коллеги, прошу помощи в решении задачи на VBA. Требуется скопировать ячейку n раз вниз через каждые m строк.
Преобразование времени в число Power Query
 
Добрый день! Коллеги, нужна ваша помощь с Power Query. Необходимо преобразовать время из формата времени в числовой формат. Во файле-примере есть 3 столбца
time = это значение времени, которое необходимо преобразовать (формат из корпоративной отчетности)
37:30:55 = это то же самое время, но в формате Excel "37:30:55"
number = это итоговый результат, который нужно получить с помощью PQ
Вроде все просто, открываем PQ и просто умножаем time на 24, можно даже тип данных не менять и полученное значение совпадет с эталонным из столбца number. Это я и сделал в запросе "Эта книга".

Однако, если открыть PQ не в этом же файле а сослаться на него через "Из файла" -> "Из Excel" (запрос "Из файла"), то формат столбцов time и 37:30:55 будет отличаться от формата из запроса Эта книга. Просто так умножить значение этих столбов на 24 мы больше не можем. Для умножения необходимо менять тип данных. Я изменил тип данных в столбце time на тип number, а в столбце 37:30:55 на тип time. Но после умножения на 24 ни один из получившихся результатов не совпал с эталоном из столбца number. Возможно это баг, либо я чего то не понимаю. Подскажите пожалуйста как правильно произвести преобразование в запросе "Из файла"?
Перемещение значений внутри таблицы с помощью Power Query
 
Добрый день! Коллеги, нужна ваша помощь с Power Query. В файле-примере есть несколько таблиц:
general - главная таблица с данными, в которой содержится информация по каждому сотруднику в разрезе дней
logon - таблица со временем работы сотрудников в день в разрезе навыков
chat - таблица с общим временем работы на чате в разрезе дней
Необходимо разбить данные из таблицы general на навыки, пропорционально времени [Logged On Time] из таблицы logon. Это у меня получилось заколхозить.

Теперь самое сложное, нужно отнести все значения из столбцов [Ring Time], [Время разговора (Talk Time)], [Время удержания (Hold Time)], [Wrapup Time] и [Время ожидания вызова] к навыку voice. Т. е. в столбце [Ring Time] для навыка voice за 01.02 должно быть сумма значений [Ring Time] для навыков voice + chat + offline за этот же день. Соответственно в [Ring Time] для навыков chat и offline должно быть null и т. д. Данные в остальных столбцах остаются как есть.
А данные из таблицы chat нужно вставить в столбец [Время разговора (Talk Time)] в навык chat, после того, как данные из этого столбца мы перенесем в навык voice. Ума не приложу как вообще можно сделать подобное.
Изменено: Murderface_ - 12 фев 2020 16:07:32 (Добавил таблицу как должен выглядеть итоговый вариант)
Добавление в таблицу столбца, соответствующему максимальному значению с помощью Power Query
 
Добрый день! Коллеги, прошу вашей помощи в решении задачи с помощью Power Query. В файле-примере есть выгрузка из отчетности с несколькими сотрудниками, а также таблица соответствия, подключенных у сотрудников skills к определенным навыкам. Необходимо определить какой навык обслуживал каждый сотрудник наибольшее время за каждый день, исходя из времени по столбцу "Logged On Time". В файле-примере, что-то заколхозил, но получилось, как мне кажется, слишком много действий.

Возможно ли решить данную задачу, например, просто через добавление столбца, в котором произвести соответствующие преобразования?
Математические операции сразу с несколькими столбцами таблицы в Power Query
 
Коллеги, добрый день! Прошу помощи в решении задачи с помощью Power Query. В таблице из примера содержатся секунды в числовом формате. Для преобразования их в формат времени, необходимо разделить все столбцы, начиная с 3, на 86400. С помощью мышки можно это сделать только для каждого столбца по отдельности, что не очень удобно. Подскажите как правильно выполнить данную операцию со всеми, требуемыми столбцами? Смог сформировать список столбцов, которые нужно поделить на 86400, а что дальше с ним делать не знаю.
Рассчитать время исходящих вызовов в Power Query
 
Коллеги, добрый день! Прошу помощи в решении задачи. Необходимо рассчитать время исходящих вызовов с помощью PQ. В файле-примере есть несколько таблиц таблиц:
Таблица data это исходные данные.
Таблица Решение это это решение задачи с помощью формул.
В запросе PQ попытался реализовать ту же логику расчета, что и с помощью формул. Вроде даже цифры сходятся, но если в таблице с исходными данными в столбце I удалить отмеченные желтым ячейки, то PQ вместо результата выдаст пустоту. Оно и понятно, ведь когда мы удалим 0 в столбце I, то PQ воспримет их как null, а все математические операции с null возвращают null. Подскажите пожалуйста как это решить? Также при решении задачи в PQ использовал функцию Table.Group(), возможно есть более красивое решение.
Группировка по условию в Power Query
 
Добрый день! Коллеги, прошу помощи для решения задачи с помощью PQ. Необходимо в разрезе дней посчитать сумму всех значений из столбца "Число1" и отдельно сумму значений из столбца "Число2", с условием [Критерий] = "Критерий 6". За каждый день значение должно быть = 100. Подозреваю, что все это можно выполнить с помощью функции Table.Group(), только не знаю как задать условие с критерием. Подскажите пожалуйста как правильно?
Преобразование "штатки" из 1С в человеческий вид с помощью Power Query
 
Добрый день! Делаю форму для преобразование "штатки" из 1С в нормальный вид. Дошел до шага, где необходимо преобразовать ставку из текстового формата (3/4) в числовой (0,75). Смог сделать это с помощью добавление отдельного столбца
Код
= Table.AddColumn(filter, "Ставка", each Number.From(Text.Start([Количество ставок], 1)) / Number.From(Text.End([Количество ставок], 1)))
но уверен, что можно обойтись и без него. Пытался через функцию Table.ReplaceValue, но что то делаю не так. Подскажите пожалуйста как правильно?
Счет количества предложений в разрезе статусов после удаления дубликатов DAX
 
Добрый день! Коллеги, нужная ваша помощь в решении задачи по Power Pivot. В примере из вложения необходимо расчитать количество предложений в разбивке по статусам. Есть несколько Но:
1) в примере могут повторяющиеся номера и наименования с разными статусами (Клиент думает, Не предлагалось)
2) в примере могут повторяющиеся номера и наименования с разными статусам, относящимися к предложениям (Клиент не заинтересован, Клиент согласен)
Для расчета всех предложений необходимо сперва отфильтровать данные по статусу <> "Не предлагалось", затем удалить дубликаты по номеру и наименованию. Затем мы просто считаем количество обращений в разрезе статусов. В примере эта фильтрация и удаление дубликатов сделана в PQ и меры с детализацией предложений имеют подпись "эталон".

Необходимо реализовать такой же расчет без использования PQ через DAX. С общим количеством предложений все просто
Код
=COUNTROWS(SUMMARIZE(FILTER(data;data[Статус]<>"Не предлагалось");data[Номер];data[Наименование]))
фильтруем данные по той же логике и считаем строки без дубликатов, но при попытке разбить предложения по статусам не удается добиться желаемого результата.
Изменено: Murderface_ - 17 янв 2020 12:27:51
Объединение таблиц по нескольким столбцам в Power Query
 
Добрый день! Коллеги, подскажите пожалуйста как решить задачу через Power Query. В примере есть 2 таблицы, каждая из которых содержит 2 столбца с одинаковыми названиями День и Очередь. Необходимо объединить таблицы так, чтобы при совпадении данных в столбцах День и Очередь, значения из 3 столбца (Количество обращений и Автоматически) находились в одной строке. Сложность в том, что в одной таблице не всегда могут быть все те очереди, которые есть в другой таблице. В примере есть таблица, как должен выглядеть итоговый результат. Не смог добиться его самостоятельно.
[Power Query] Расчет размера вознаграждения, зависящий от количества всех операций в разных таблицах
 
Добрый день, коллеги! У меня вопрос, касающийся PQ. Необходимо произвести расчет вознаграждения, где размер вознаграждения это произведение стоимости и коэффициента (k). Сам коэффициент (k) зависит от количества всех операций, в моем примере их 2 таблицы.

Что я сделал:
В запросе "k" я вывел коэффициент, подсчитав общее количество операций в обеих таблицах с помощью Table.RowCount(). Но при попытке умножить получившийся коэффициент на стоимость в любой из таблиц появляется ошибка "Expression.Error: В ходе вычислений была обнаружена циклическая ссылка.". Подскажите пожалуйста как можно решить данную проблему?
Поиск значений из списка в строке с текстом Power Query
 
Добрый день! Коллеги, прошу помощи в решении задачи с помощью Power Query. Во вложении файл с 2 таблицами "Текст" и "Список опций". Необходимо промаркеровать (например с помощью 1) те строки из таблицы "Текст", в которых содержится хотя бы одна текстовая строка из таблицы "Список опций". При этом поиск необходимо осуществлять к конца строки (при наличие разделителя " на " в строке выполнять поиск после него) и исключить влияние регистра.

Для исключения влияния регистра можно использовать функцию Text.Lower() на обе таблицы, а для поиска с конца развернуть текст с помощью Text.Reverse(). А вот что делать дальше ума не приложу.
Изменено: Murderface_ - 17 дек 2019 15:01:38
Преобразование табеля в плоскую таблицу с помощью Power Query
 
Добрый день! Коллеги, прошу помощи в решении задачи. Есть типовой табель, который нужно преобразовать в плоскую таблицу. Файл во вложении. Кое что уже сделал самостоятельно, осталось к столбцам , ФИО, Табельный номер добавить 3 столбца Дата, Вид рабочего времени и Табельное время. Последние 2 можно разделить по маркеру "Столбцы", а дата это столбцы с 1 по 31.

Вроде бы с помощью инструмента "Отменить свертывание столбцов" должно все решаться, но без дополнительных преобразований не удается добиться желаемого результата.
Изменено: Murderface_ - 26 ноя 2019 10:06:02
Почему после вычислений не совпадает Дата + Время
 
Добрый день! Коллеги, столкнулся с непонятной для меня проблемой. Пример во вложении.

В столбце B московское дата+время из столбца A переводится в уральское с округлением вниз до получаса. По получившемуся времени в столбце C происходит поиск показателя в другой таблице через ИНДЕКС + ПОИСКПОЗ, но в некоторых индервалах появляется ошибка #Н/Д. Однако, если мы из таблицы с показателем с помощью ВПР с интервальным просмотром подтянем дату + время, а затем по этой дате время через ИНДЕКС + ПОИСКПОЗ точно также выполним поиск показателя, то ошибок не будет. Можно предположить, что ошибка может быть в преобразовании московского времени в уральское, но время в столбцах B и D одинаковое.

Не понимаю почему так происходит.
[Power Query] Можно продавать или нет в зависимости от 4x условий
 
Добрый день! Коллеги, прошу помощи в решении задачи на PQ. Во вложении файл, в котором необходимо определить можно ли осуществлять продажу или нет в зависимости от условий:

1) Если ID один, то если Если Исключение = 1, то НЕТ, если Исключение = 0, то ДА
2) Если ID несколько и хотя бы для одного из них Исключение = 1, то для каждого ID НЕТ
3) Если ID несколько и для каждого из них Исключение = 0, а Статус каждого из них  = "Не предлагалось", то для всех ДА
4) Если ID несколько и для каждого из них Исключение = 0, а Статус <> "Не предлагалось", то НЕТ для строк со статусом "Не предлагалось", а для остальных строк ДА

Написал громоздкую формулу в столбце AA, которая учитывает эти условия, но файл очень сильно тормозит при большом объеме данных. Хотелось бы реализовать все эти условия в PQ, но знаний для этого недостаточно. Мне почему то кажется, что задачу можно решить через группировку, но, возможно, я ошибаюсь.
Изменено: Murderface_ - 18 ноя 2019 15:42:34
В каких случаях использовать функцию Table.Buffer в PQ?
 
Добрый день! Прошу поделиться опытом, в каких случаях можно использовать функцию Table.Buffer в Power Query для ускорения обработки запросов.

Прочитал несколько статей про это. Понял, что благодаря этой функции таблица загружается в оперативную память и дальнейшие обращения к этой таблице происходят быстрее, чем при обращении к сетевому диску например. Сейчас же я пытаюсь понять как применить эту функцию в моем случае. Ниже приведена схема зависимостей запроса.

Скрытый текст


Сотрудники = обычная плоская таблица формата (Сотрудник, Город). Только подключение.
Transform = функция, которая подставляет город из таблицы Сотрудники к исходным данным + наводит красоту.
Исходные данные = результат применения функции Transform к каждому файлу с исходными данными и последующая сборка этих данных в одну таблицу (около 1,9 миллионов строк). Только подключение.
А уже последующие 4 запроса обращаются к таблице Исходные данные. Два из них загружены в модель данных, остальные 2 на лист в виде таблиц.

И тут возникают вопросы о том, где применять Table.Buffer.

Таблица Сотрудники. В последнем шаге самого запроса Сотрудники, в функции Transform в шаге где идет обращение к таблице Сотрудники или же в запросе Исходные данные в шаге где применяется функция Transform?

Таблица Исходные данные. Т. к. к ней в дальнейшем происходит 4 других запроса, то думаю, что функция Table.Buffer может ускорить работу. Но при попытке применить функцию в последнем шаге запроса Исходные данные PQ выдает ошибку о недостатке оперативной памяти (ее к слову 16 Гб, но офис 2013 32 битный). Подскажите пожалуйста где в данном случае применение функции Table.Buffer будет иметь смысл?
Изменено: Murderface_ - 15 ноя 2019 13:13:22
Пользовательская функция в PQ. Расчет бонусов в зависимости от количества часов
 
Добрый день! Прошу подсказать как можно решить задачу в PQ. Необходимо начислить сотрудникам баллы. За каждые полные 4 часа начисляется 7 баллов, максимум 42. Можно конечно с помощью целочисленного деления получить число и умножить его на 7, ограничив максимальное количество баллов, но есть 2 нюанса:
1) эти условия могут измениться
2) эти условия будут участвовать в нескольких запросах
И в случае изменений придется править каждое условие в каждом запросе, это неудобно.

Подскажите, можно ли решить задачу с помощью созданного Параметра, чтобы в случае изменений править только его?
[Power Query] Удаление строк по условию (содержание текста в предыдущих строках) со смещением
 
Добрый день! Коллеги, прошу вашей помощи в решении задачи через PQ.

В примере есть один столбец, в котором содержаться стоки с текстом "Что сохранить" и "Что улучшить". После каждой из этих строк идет строка с рекомендациями (текст этих строк я заменил на кашу из букв и цифр из-за возможного наличия там конфиденциальной информации).

Задача заключается в том, чтобы удалить эти строки, т. е. при нахождении строки с текстом "Что сохранить" или "Что улучшить" удалялась эта строка и следующая за ней. По моему скромному мнению для решения задачи нужно создать столбец с маркерами, а затем по этим маркерам выполнить удаление. И у меня большая просьба к знатокам языка M, пожалуйста не умещайте весь код в одну строку, я хочу полученное решение применить на рабочем файле самостоятельно  :)  
Страницы: 1 2 След.
Наверх