Public Function ИзвлечьДатуДокумента(Text As String) As Date
ИзвлечьДатуДокумента = Format(CDate(MyRegex(Text, "\d{2}\.\d+\.\d+")), "DD.MM.YYYY")
'ИзвлечьДатуДокумента = Format(MyRegex(Text, "\d{2}\.\d+\.\d+"), "DD.MM.YYYY")
End Function
Данная формула извлекает дату из выбранной ячейки. Однако возникает проблема с возвратом значения. Если возвращать дату то идет цифровое значение даты. Если поставить String то возвращается текст и не группируются данные в месяца и года. Может есть возможность как то это побороть без изменения числового формата????
Оптимизация загрузки (слияние файлов PQ)
Пользователь
Сообщений: Регистрация: 22.07.2017
28.04.2025 10:52:38
Добрый день!
У меня вопрос возможно ли оптимизировать запросы? У меня есть папка в папке около 20 файлов как итог. В этих файлах 5 листов и на каждом листе таблица. У меня созданы 5 запросов. Которые собирают со всех файлов информацию по листам ("Доставлено", "Возвращено" и т.д.) в один файл в на пять листов. Общая сумма строк которая импортируется в файл не превышает 200к. Однако все происходит долго и иногда завершается с ошибкой. Ниже пример двух запросов.
Код
let
Источник = Folder.Files(#"Адрес"),
#"Отфильтрованные скрытые файлы1" = Table.SelectRows(Источник, each [Attributes]?[Hidden]? <> true),
#"Вызвать настраиваемую функцию1" = Table.AddColumn(#"Отфильтрованные скрытые файлы1", "Преобразовать файл", each #"Преобразовать файл"([Content])),
#"Переименованные столбцы1" = Table.RenameColumns(#"Вызвать настраиваемую функцию1", {"Name", "Source.Name"}),
#"Другие удаленные столбцы1" = Table.SelectColumns(#"Переименованные столбцы1", {"Source.Name", "Преобразовать файл"}),
#"Столбец расширенной таблицы1" = Table.ExpandTableColumn(#"Другие удаленные столбцы1", "Преобразовать файл", List.Distinct(List.Combine(List.Transform (#"Другие удаленные столбцы1"[#"Преобразовать файл"], Table.ColumnNames)))),
#"Замененное значение" = Table.ReplaceValue(#"Столбец расширенной таблицы1",null,0,Replacer.ReplaceValue,{"Цена c НДС без учёта скидок, руб. за шт.", "Ваша скидка по акции маркетплейса, руб. на 1 шт.", "Ваша скидка по бонусам СберСпасибо (за шт.), руб. на 1 шт.", "Ваша скидка по баллам Яндекс.Плюса, руб. на 1 шт.", "Цена с НДС с учётом всех скидок, руб. за шт.", "Стоимость всех доставленных штук с НДС без учёта скидок, руб.", "Сумма всех скидок для доставленных штук, руб.", "Стоимость всех доставленных штук с НДС с учётом всех скидок, руб.", "Цена c НДС без учёта скидок за шт., ₽", "Ваша скидка по акции маркетплейса на 1 шт., ₽", "Ваша скидка по бонусам СберСпасибо (за шт.) на 1 шт., ₽", "Ваша скидка по баллам Яндекс.Плюса на 1 шт., ₽", "Цена с НДС с учётом всех скидок за шт., ₽", "Стоимость всех доставленных штук с НДС без учёта скидок, ₽", "Сумма всех скидок для доставленных штук, ₽", "Стоимость всех доставленных штук с НДС с учётом всех скидок, ₽"}),
#"Добавлен пользовательский объект" = Table.AddColumn(#"Замененное значение", "Стоимость всех доставленных штук с НДС без учёта скидок", each [#"Стоимость всех доставленных штук с НДС без учёта скидок, руб."]+[#"Стоимость всех доставленных штук с НДС без учёта скидок, ₽"]),
#"Добавлен пользовательский объект1" = Table.AddColumn(#"Добавлен пользовательский объект", "Сумма всех скидок для доставленных штук", each [#"Сумма всех скидок для доставленных штук, руб."]+ [#"Сумма всех скидок для доставленных штук, ₽"]),
#"Добавлен пользовательский объект2" = Table.AddColumn(#"Добавлен пользовательский объект1", "Стоимость всех доставленных штук с НДС с учётом всех скидок", each [#"Стоимость всех доставленных штук с НДС с учётом всех скидок, руб."]+[#"Стоимость всех доставленных штук с НДС с учётом всех скидок, ₽"]),
#"Другие удаленные столбцы" = Table.SelectColumns(#"Добавлен пользовательский объект2",{"Source.Name", "Номер заказа", "Тип заказа", "Название товара", "Ваш SKU", "SKU на складе", "Количество переданных в доставку, шт.", "Доставлено, шт.", "Дата оформления заказа", "Дата передачи товара в доставку", "Дата доставки товара", "Способ оплаты", "Ставка НДС", "Стоимость всех доставленных штук с НДС без учёта скидок", "Сумма всех скидок для доставленных штук", "Стоимость всех доставленных штук с НДС с учётом всех скидок"}),
#"Измененный тип" = Table.TransformColumnTypes(#"Другие удаленные столбцы",{{"Дата оформления заказа", type date}, {"Дата передачи товара в доставку", type date}, {"Дата доставки товара", type date}})
in
#"Измененный тип"
Код
let
Источник = Folder.Files(#"Адрес"),
#"Отфильтрованные скрытые файлы1" = Table.SelectRows(Источник, each [Attributes]?[Hidden]? <> true),
#"Вызвать настраиваемую функцию1" = Table.AddColumn(#"Отфильтрованные скрытые файлы1", "Преобразовать файл (4)", each #"Преобразовать файл (4)"([Content])),
#"Переименованные столбцы1" = Table.RenameColumns(#"Вызвать настраиваемую функцию1", {"Name", "Source.Name"}),
#"Другие удаленные столбцы1" = Table.SelectColumns(#"Переименованные столбцы1", {"Source.Name", "Преобразовать файл (4)"}),
#"Столбец расширенной таблицы1" = Table.ExpandTableColumn(#"Другие удаленные столбцы1", "Преобразовать файл (4)", List.Distinct(List.Combine(List.Transform (#"Другие удаленные столбцы1"[#"Преобразовать файл (4)"], Table.ColumnNames)))),
#"Замененное значение" = Table.ReplaceValue(#"Столбец расширенной таблицы1",null,0,Replacer.ReplaceValue,{"Цена c НДС без учёта скидок, руб. за шт.", "Ваша скидка по акции маркетплейса, руб. на 1 шт.", "Ваша скидка по бонусам СберСпасибо (за шт.), руб. на 1 шт.", "Ваша скидка по баллам Яндекс.Плюса, руб. на 1 шт.", "Цена с НДС с учётом всех скидок, руб. за шт.", "Стоимость всех переданных в доставку штук с НДС без учёта скидок, руб.", "Сумма всех скидок для переданных в доставку штук, руб.", "Стоимость всех переданных в доставку штук с НДС с учётом всех скидок, руб.", "Цена c НДС без учёта скидок за шт., ₽", "Ваша скидка по акции маркетплейса на 1 шт., ₽", "Ваша скидка по бонусам СберСпасибо (за шт.) на 1 шт., ₽", "Ваша скидка по баллам Яндекс.Плюса на 1 шт., ₽", "Цена с НДС с учётом всех скидок за шт., ₽", "Стоимость всех переданных в доставку штук с НДС без учёта скидок, ₽", "Сумма всех скидок для переданных в доставку штук, ₽", "Стоимость всех переданных в доставку штук с НДС с учётом всех скидок, ₽"}),
#"Добавлен пользовательский объект" = Table.AddColumn(#"Замененное значение", "Стоимость всех переданных в доставку штук с НДС без учёта скидок", each [#"Стоимость всех переданных в доставку штук с НДС без учёта скидок, руб."]+[#"Стоимость всех переданных в доставку штук с НДС без учёта скидок, ₽"]),
#"Добавлен пользовательский объект1" = Table.AddColumn(#"Добавлен пользовательский объект", "Сумма всех скидок для переданных в доставку штук", each [#"Сумма всех скидок для переданных в доставку штук, руб."]+[#"Сумма всех скидок для переданных в доставку штук, ₽"]),
#"Добавлен пользовательский объект2" = Table.AddColumn(#"Добавлен пользовательский объект1", "Стоимость всех переданных в доставку штук с НДС с учётом всех скидок", each [#"Стоимость всех переданных в доставку штук с НДС с учётом всех скидок, руб."]+[#"Стоимость всех переданных в доставку штук с НДС с учётом всех скидок, ₽"]),
#"Другие удаленные столбцы" = Table.SelectColumns(#"Добавлен пользовательский объект2",{"Source.Name", "Номер заказа", "Тип заказа", "Название товара", "Ваш SKU", "SKU на складе", "Количество переданных в доставку, шт.", "Дата оформления заказа", "Дата передачи товара в доставку", "Дата доставки товара", "Способ оплаты", "Ставка НДС", "Стоимость всех переданных в доставку штук с НДС без учёта скидок", "Сумма всех скидок для переданных в доставку штук", "Стоимость всех переданных в доставку штук с НДС с учётом всех скидок"}),
#"Измененный тип" = Table.TransformColumnTypes(#"Другие удаленные столбцы",{{"Дата оформления заказа", type date}, {"Дата передачи товара в доставку", type date}, {"Дата доставки товара", type date}})
in
#"Измененный тип"
Слияние таблиц имеющие различные шапки (изменена форма выгрузки)
Пользователь
Сообщений: Регистрация: 22.07.2017
16.04.2025 12:48:35
Добрый день! Есть проблема с отчетам который предоставляет внешний контрагент. Хочу объединить два файла с использованием PQ, у них различные шапки. 1. В файле март добавлен столбец "Ваш номер заказа"; 2. Изменены наименования столбцов например с "Стоимость всех переданных в доставку штук с НДС с учётом всех скидок, руб." на "Стоимость всех переданных в доставку штук с НДС с учётом всех скидок, ₽"
При слиянии происходит что данные не подтягиваются из файла март не подтягиваются в исходную таблицу.
Прошу подсказать как можно сделать . 1. Чтобы новые столбцы создались в сведенной таблице. И данные которые начались с марта туда подтянулись. 2. Как верно произвести переименование столбцов с "Стоимость всех переданных в доставку штук с НДС с учётом всех скидок, руб." на "Стоимость всех переданных в доставку штук с НДС с учётом всех скидок, ₽" чтобы данные также объединились в общую таблицу.
Неверное определение размера таблицы при получении запроса из книги
Пользователь
Сообщений: Регистрация: 22.07.2017
25.02.2025 14:59:57
Добрый день! Получаю выгрузку из Интернет Магазины с отчетом о продажах. Хочу все отчеты с помощью PQ объединить в один файл. Однако возникает неверное определение размера таблицы и как итог в PQ попадают только сведения о шапке таблицы. Прошу помочь в решении данного вопроса.
let
Источник = Excel.Workbook(File.Contents("C:\Users\nexta\Desktop\united_statistics_report_60067698_11-11-2024.xlsx"), null, true),
#"Измененный тип" = Table.TransformColumnTypes(#"Возвращенные товары_Sheet",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}, {"Column20", type text}, {"Column21", type text}, {"Column22", type text}, {"Column23", type text}, {"Column24", type text}, {"Column25", type text}, {"Column26", type text}, {"Column27", type text}, {"Column28", type text}, {"Column29", type text}, {"Column30", type text}, {"Column31", type text}, {"Column32", type text}, {"Column33", type text}, {"Column34", type text}, {"Column35", type text}, {"Column36", type text}, {"Column37", type text}, {"Column38", type text}, {"Column39", type text}})
in
#"Измененный тип"
Power Query замена значений в выбранных колонках на отрицательные при соблюдения условий в строке
Пользователь
Сообщений: Регистрация: 22.07.2017
01.07.2024 09:18:25
Добрый день! Существует проблема с отчетом. Выгружен отчет где все цифры имеют положительные значения. В столбце "J" установлен статус это "Продажа" или "Возврат". Соответственно значения в строках "Продажа" должны иметь исходные значения, а вот в строках "Возврат" необходимо произвести умножения на значения "-1".
При в начальном параметре замена значений или стандартные операции нет возможности добавить условия по строке. Заменить значения нужно только в некоторых колонках (в предоставленном примере выделенные зеленой заливкой) Может у кого есть идеи как это можно сделать. Заранее спасибо за помощь
Ошибка преобразование данных при создании функции в Power Query,, При создании функции поля имеют имеют идентичные типы, однако не понимаю почему идет дополнительное преобразование полей. В результате чего поля принимают значения Error и расчет не производится.
Пользователь
Сообщений: Регистрация: 22.07.2017
29.02.2024 14:35:09
Добрый день!
Столбцы Товар и Подробности имеют тип текст text, остальные столбцы имеют тип number.
При создании функции, где в расчет берутся все столбцы , выдает сообщение
Expression.Error: Не удается преобразовать значение null в тип Text.
Сведения:
Value=
Type=[Type]
Однако я не понимаю почему преобразуется столбец если столбец изначально имеет тип значения текст.
И как лучше обойти данную ошибку???
[ Закрыто] Power Query, Ошибка преобразование данных
Пользователь
Сообщений: Регистрация: 22.07.2017
29.02.2024 13:08:07
Добрый день!
Столбцы Товар и Подробности имеют тип текст text, остальные столбцы имеют тип number.
При создании формулы где в расчет берутся все столбцы , выдает сообщение
Expression.Error: Не удается преобразовать значение null в тип Text.
Сведения:
Value=
Type=[Type]
Однако я не понимаю почему преобразуется столбец если столбец изначально имеет тип значения текст.
И как лучше обойти данную ошибку???
Изменение настроек умной таблицы Power Query
Пользователь
Сообщений: Регистрация: 22.07.2017
16.11.2017 22:59:16
Добрый день, форумчане! посмотрев полезные трюки от Николая Павлова решил сделать сборку таблиц в единый файл с помощью power query. И вроде бы все получилось. Таблицу полученную необходимо печатать и при этом ячейки содержат большое количество символов. Я топорным способом в полученный результат из power query в самом excel уменьшил длину строк и сделал перенос слов и все уместил по ширине листа и вроде все хорошо, но при обновлении сбиваются настройки ширины листа и все встаёт на исходные позиции , а это очень сильно удручает. Может подскажите как можно зафиксировать ширину строк в excel ? Искал настройки и ничего не нашёл
Неверный расчет формулы power query
Пользователь
Сообщений: Регистрация: 22.07.2017
22.07.2017 19:19:17
Добрый день, уважаемые форумчане подскажите почему power query высчитывает формулу неверно. Есть 6 единиц товара1с общей стоимостью 27,15 usd. При делении данной позиции в power query выдает себестоимость единицы в 4,42, хотя правильное значение 4,525 и согласно правилам математики округление должно производится в сторону 4,53 USD. Если смотреть на расчет в power query до действия округления значения, в столбце Факт с/с расчет 4,52499995, вместо 4,525. Вопрос как это можно исправить чтобы Excel рас читывал верно значения. Заранее благодарю за советы и наставления
