Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Пользователи » asdewq1
Выбрать дату в календаре…Выбрать дату в календаре

Страницы: 1
Универсальная формула трансформация текста в числа в PQ
 
#
12.09.2025 14:25:43
Добрый день!
Может у кого то есть универсальная формула в PQ по трансформации банковских значений чисел  в обычный формат числа.
Просто в различных банках могут использовать как точки и запятые или тире для обозначения копеек.
Заранее спасибо
 
Перейти    
Возврат Даты в числовом виде VBA
 
#
12.05.2025 11:51:43
Добрый день!
Написал формулу на VBA
Код
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)
 
#
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
    #"Измененный тип"
 
Перейти    
Слияние таблиц имеющие различные шапки (изменена форма выгрузки)
 
#
16.04.2025 12:48:35
Добрый день!
Есть проблема с отчетам который предоставляет внешний контрагент. Хочу объединить два файла с использованием PQ, у них различные шапки.
1. В файле март добавлен столбец "Ваш номер заказа";
2. Изменены наименования столбцов например с "Стоимость всех переданных в доставку штук с НДС с учётом всех скидок, руб." на "Стоимость всех переданных в доставку штук с НДС с учётом всех скидок, ₽"


При слиянии происходит что данные не подтягиваются из файла март не подтягиваются в исходную таблицу.

Прошу подсказать как можно сделать .
1. Чтобы новые столбцы создались  в сведенной таблице. И данные которые начались с марта туда подтянулись.
2. Как верно произвести переименование столбцов  с "Стоимость всех переданных в доставку штук с НДС с учётом всех скидок, руб." на "Стоимость всех переданных в доставку штук с НДС с учётом всех скидок, ₽" чтобы данные также объединились в общую таблицу.  
 
Перейти    
Неверное определение размера таблицы при получении запроса из книги
 
#
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),

   #"Возвращенные товары_Sheet" = Источник{[Item="Возвращенные товары",Kind="Sheet"]}[Data],

   #"Измененный тип" = 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 замена значений в выбранных колонках на отрицательные при соблюдения условий в строке
 
#
01.07.2024 09:18:25
Добрый день!
Существует проблема с отчетом.
Выгружен отчет где все цифры имеют положительные значения.
В столбце "J"  установлен статус это "Продажа" или "Возврат". Соответственно значения в строках "Продажа" должны иметь исходные значения, а вот в строках "Возврат" необходимо произвести умножения на значения "-1".

При в начальном параметре замена значений или стандартные операции нет возможности добавить условия по строке.
Заменить значения нужно  только в некоторых колонках (в предоставленном примере выделенные зеленой заливкой)
Может у кого есть идеи как это можно сделать.
Заранее спасибо за помощь  
 
Перейти    
Ошибка преобразование данных при создании функции в Power Query,, При создании функции поля имеют имеют идентичные типы, однако не понимаю почему идет дополнительное преобразование полей. В результате чего поля принимают значения Error и расчет не производится.
 
#
29.02.2024 14:35:09
Добрый день!

Столбцы Товар и Подробности имеют  тип текст text, остальные столбцы имеют тип number.


При создании функции, где в расчет берутся все столбцы , выдает сообщение


Expression.Error: Не удается преобразовать значение null в тип Text.

Сведения:

  Value=

  Type=[Type]

Однако я не понимаю  почему преобразуется столбец если столбец изначально имеет тип значения текст.

И как лучше обойти данную ошибку???
 
Перейти    
[ Закрыто] Power Query, Ошибка преобразование данных
 
#
29.02.2024 13:08:07
Добрый день!

Столбцы Товар и Подробности имеют  тип текст text, остальные столбцы имеют тип number.


При создании формулы где в расчет берутся все столбцы , выдает сообщение


Expression.Error: Не удается преобразовать значение null в тип Text.

Сведения:

   Value=

   Type=[Type]

Однако я не понимаю  почему преобразуется столбец если столбец изначально имеет тип значения текст.

И как лучше обойти данную ошибку???
 
Перейти    
Изменение настроек умной таблицы Power Query
 
#
16.11.2017 22:59:16
Добрый день, форумчане!
посмотрев полезные трюки от Николая Павлова решил сделать сборку таблиц в единый файл с помощью power query.
И вроде бы все получилось. Таблицу полученную необходимо печатать и при этом ячейки содержат большое количество символов. Я топорным способом в  полученный результат из power query  в самом excel уменьшил длину строк и сделал перенос слов и все  уместил по ширине листа и вроде все хорошо, но при обновлении сбиваются настройки ширины листа и все встаёт на исходные позиции , а это очень сильно удручает. Может подскажите как можно зафиксировать ширину строк в excel ?
Искал настройки и ничего не нашёл  
 
Перейти    
Неверный расчет формулы power query
 
#
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 рас читывал верно значения.
Заранее благодарю за советы и наставления
Изменено: asdewq1 - 22.07.2017 19:19:54
 
Перейти    
Страницы: 1
Наверх