Страницы: 1
RSS
Выгрузка данных из запроса PQ, несоответствие данных в сводной таблице из запроса
 
Доброго времени суток уважаемые форумчане!

Прошу поделиться мыслями для решения следующего вопроса:

Имеется простой запрос PQ, собирающий общую таблицу данных из ~200 файлов Excel. Там данные сортируются по датам, удаляются дубликаты и на выходе имеем какое-то кол-во правильных (нужных) данных. Проблема в том, что если выгрузить данные на лист Excel, то они полностью соответствуют данным последнего шага запроса, НО! если выгрузить данные в виде сводной таблицы, используя запрос в качестве источника, то выгружаются "левые" данные - не соответствующие тому, что имели бы на листе Excel в виде выгрузки. Что может быть причиной такого казуса?
 
RVA,в сводной количество строк меньше чем в таблице обычной выгрузки на лист?
Собираетесь подкрепить свои слова файлом-примером или, на худой конец, скриншотами?
 
Код
let
    Источник = Folder.Files("\\db02\Documents AXAPTA\ExpToEllipse\"),
    #"Строки с примененным фильтром" = Table.SelectRows(Источник, each ([Extension] = ".xls")),
    #"Строки с примененным фильтром1" = Table.SelectRows(#"Строки с примененным фильтром", each not Text.StartsWith([Name], "~")),
    #"Строки с примененным фильтром3" = Table.SelectRows(#"Строки с примененным фильтром1", each Text.Contains([Name], "INV")),
    #"Удаленные столбцы" = Table.RemoveColumns(#"Строки с примененным фильтром3",{"Name", "Extension", "Date accessed", "Date modified", "Attributes", "Folder Path"}),
    #"Отфильтрованные скрытые файлы1" = Table.SelectRows(#"Удаленные столбцы", each [Attributes]?[Hidden]? <> true),
    #"Вызвать настраиваемую функцию1" = Table.AddColumn(#"Отфильтрованные скрытые файлы1", "Преобразовать файл из Подгрузка номенклатуры (цены)", each #"Преобразовать файл из Подгрузка номенклатуры (цены)"([Content])),
    #"Столбец расширенной таблицы1" = Table.ExpandTableColumn(#"Вызвать настраиваемую функцию1", "Преобразовать файл из Подгрузка номенклатуры (цены)", Table.ColumnNames(#"Преобразовать файл из Подгрузка номенклатуры (цены)"(#"Пример файла"))),
    #"Удаленные столбцы1" = Table.RemoveColumns(#"Столбец расширенной таблицы1",{"Content", "CHANGED_BY1I", "Column4"}),
    #"Сортированные строки1" = Table.Sort(#"Удаленные столбцы1",{{"STOCK_CODE1I", Order.Ascending}}),
    #"Сортированные строки" = Table.Sort(#"Сортированные строки1",{{"Date created", Order.Descending}}),
    #"Измененный тип" = Table.TransformColumnTypes(#"Сортированные строки",{{"INVT_VAL_B1I", type number}, {"STOCK_CODE1I", type text}}),
    #"Добавлен индекс" = Table.AddIndexColumn(#"Измененный тип", "Индекс", 1, 1),
    #"Удаленные дубликаты" = Table.Distinct(#"Добавлен индекс", {"STOCK_CODE1I"}),
    #"Строки с примененным фильтром2" = Table.SelectRows(#"Удаленные дубликаты", each ([INVT_VAL_B1I] <> null)),
    #"Удаленные столбцы2" = Table.RemoveColumns(#"Строки с примененным фильтром2",{"Индекс"}),
    #"Измененный тип1" = Table.TransformColumnTypes(#"Удаленные столбцы2",{{"Date created", type date}}),
    #"Переименованные столбцы3" = Table.RenameColumns(#"Измененный тип1",{{"Date created", "Дата выгрузки с Аксапты"}}),
#"Измененный тип5" = Table.Buffer(Table.TransformColumnTypes(#"Переименованные столбцы3",{{"INVT_VAL_B1I", type number}})),
    #"Объединенные запросы" = Table.NestedJoin(#"Измененный тип5",{"STOCK_CODE1I"},#"MSF100 (2)",{"ном ном"},"MSF100 (2)",JoinKind.LeftOuter),
    #"Развернутый элемент MSF100 (2)" = Table.ExpandTableColumn(#"Объединенные запросы", "MSF100 (2)", {"код мпз", "статус"}, {"MSF100 (2).код мпз", "MSF100 (2).статус"}),
    #"Переименованные столбцы" = Table.RenameColumns(#"Развернутый элемент MSF100 (2)",{{"MSF100 (2).код мпз", "код мпз Ellipse"}, {"MSF100 (2).статус", "статус в Ellipse"}}),
    #"Измененный тип6" = Table.TransformColumnTypes(#"Переименованные столбцы",{{"STOCK_CODE1I", type text}}),
    #"Объединенные запросы1" = Table.NestedJoin(#"Измененный тип6",{"код мпз Ellipse"},#"MSF170 (2)",{"STOCK_CODE"},"MSF170 (2)",JoinKind.LeftOuter),
    #"Развернутый элемент MSF170 (2)" = Table.ExpandTableColumn(#"Объединенные запросы1", "MSF170 (2)", {"LAST_MOD_DATE", "INVENT_COST_PR"}, {"LAST_MOD_DATE", "INVENT_COST_PR"}),
    #"Переименованные столбцы1" = Table.RenameColumns(#"Развернутый элемент MSF170 (2)",{{"INVT_VAL_B1I", "Цена Аксапты"}, {"INVENT_COST_PR", "Цена Elllipse"}}),
    #"Условный столбец добавлен" = Table.AddColumn(#"Переименованные столбцы1", "Пользовательская", each if [Цена Elllipse] = [Цена Аксапты] then "ИСТИНА" else if [статус в Ellipse] = null then "Нет цены в Ellipse" else "Несовпадение цен"),
    #"Переименованные столбцы2" = Table.RenameColumns(#"Условный столбец добавлен",{{"Пользовательская", "Проверка"}}),
    #"Строки с примененным фильтром5" = Table.SelectRows(#"Переименованные столбцы2", each ([Проверка] <> "ИСТИНА")),
    #"Переименованные столбцы6" = Table.RenameColumns(#"Строки с примененным фильтром5",{{"LAST_MOD_DATE", "Дата изм. в Ellipse"}, {"STOCK_CODE1I", "Код Axapta"}}),
    #"Измененный тип2" = Table.TransformColumnTypes(#"Переименованные столбцы6",{{"Дата выгрузки с Аксапты", type text}})
in
    #"Измененный тип2"
 
RVA,  :D
А зачем вы добавили столбец индекса?
 
Суть в том, что из имеющихся на сервере ежедневных файлов-отчетов Excel я отслеживаю последнее изменение цены по материалам и сравниваю эту цену с другой базой данных. И как бы все получается при обычной выгрузке, где отражаются несоответствия. А вот если через сводную таблицу хрень какая-то. Похоже на то, в случае со сводной, шаг удаления дубликатов материалов при сортировке дат по убыванию работает не корректно. Количество элементов в сводной ненамного больше, чем в обычной выгрузке, и их даты получаются не самые свежие.
 
RVA, от запроса толку нет. Может быть станет понятнее по скринам.
По скринам выгруженной таблицы и сводной. Подкрасьте проблемное место
 
Михаил Л,

В примере 2 вкладки (сводная и просто выгрузка)

Между ними видно, что кол-во не совпадает и даты в сводной не соответствуют выгрузке. Как я писал выше в сводной таблице даты последнего изменения цен неправильные почему-то...
Изменено: RVA - 07.07.2020 07:25:18
 
RVA, не знаю причину. Сводную обновляете?
 
Михаил Л,

Конечно обновляю.
Если выгружаю на лист, то под запросом пишется - "Загружено строк: 150 (к примеру)". Если обновляю сводную. то пишет - "Загружено строк: 158" (уже больше строк и данные уже другие). Парадокс одним словом...
 
RVA,  в сводной в Код Axapta больше на 12 значений. На эти:
1961000001
1962100080
2543300011
3148359256
3149019742
3185489001
8151700002
8422120084
8452110001
8579000007
8931110002
9677160001
Изменено: Михаил Л - 07.07.2020 07:53:07
 
RVA, попробуйте использовать Table.Buffer() не в 20 строке кода, а в шаге перед удалением дубликатов, т.е. там, где вы добавляете индексы. и посмотрите что получится.
А вообще лично я дубликаты удаляю при помощи группировки. С ней никаких проблем при ленивых вычислениях не возникает как в вашем случае со связкой sort+distinct. Это глючная связка.
Вот горшок пустой, он предмет простой...
 
Вы про это?

Вы точно ВСЁ обновляете при сверке данных? Жмите "Данные - Обновить все"
Так как у вас сводная использует запрос в качестве источника данных, при обновлении сводной происходит выполнение запроса, но при этом таблица на листе не обновляется.
F1 творит чудеса
 
Блин, думал, я с ума сошел :(
Ан нет. Дело в этом, я считаю:
Цитата
RVA написал:
Похоже на то, в случае со сводной, шаг удаления дубликатов материалов при сортировке дат по убыванию работает не корректно.
У вас нет дат в таблице. У вас там текст, в чем очень легко убедиться.
F1 творит чудеса
 
Всем здравствуйте!

Максим Зеленский,
Данное кол-во относилось к последнему обновлению сводной (см. файл примера)
Весь абсурд и заключался в том, что обновляя сводную (ПКМ на сводной – обновить) показывало = 171 строка. Причем в самом запросе дат с 2019 годом было всего 3шт, а в сводной их было 14 шт, чего быть не могло!  Я сам запрос вдоль и поперек перешерстил!
Когда обновлял «Все», то на выгрузке было 160 строк. Я ничего не пил и не курил))) Мои коллеги наблюдали за этим чудом и так же удивлялись.
Даты в таблице были, просто от безысходности на последнем шаге запроса я их в текст загнал (не знал что еще сделать с этой мистикой), поскольку сводная пыталась их разложить по доп. столбцам на месяцы, кварталы и пр., думал что где-то в этом глюк.

В итоге вопрос решился радикальным способом:
1. В параметрах запроса очистил КЭШ
2. Сохранил файл
3. Закрыл Excel (с тормозами)
4. «Убил» в процессах системы Microsoft.Machup.Container - 3шт (они долго висели, не хотели выгружаться из памяти)

После запустил файл заново и все данные стали синхронно-одинаково отображаться! Ура!

Всем спасибо за уделенное внимание!
 
Михаил Л,
Столбец ИНДЕКСа я добавил для корректного удаления дубликатов после предварительной сортировки данных по дате. Как ни странно, но именно именно при предварительном индексировании удаление дубликатов работает корректно, если необходимы самые "свежие" данные в разрезе дат.  В противном случае получается хаотичное удаление дубликатов невзирая на сортировку. Как уже отметил PooHkrd, в таких случаях лучше использовать группировку, чтобы не было глюков.
 
Цитата
RVA написал:
Как ни странно, но именно именно
Здесь PQ тоже отчебучил
 
Цитата
Михаил Л написал:
PQ тоже отчебучил
Почему? Алгоритмы сортировки - они разные. Большинство быстрых - не гарантируют тот же самый порядок при равных ключевых полях сортировки. Не следует переносить опыт работы с сортировкой в Excel как единственно возможный подход где-нибудь ещё.
 
Цитата
Андрей VG написал:
Алгоритмы сортировки
Раз при небольшом количестве строк сортируется по-очередности, а при большом количестве сортируется не пойми как, то алгоритм сортировки либо отсутствует, либо алгоритм сортировки - хаос.
Должна быть какая то логика. Например, до двухсот строк - сортировка по-очередности, свыше двухсот - по-другому (и мы должны знать как именно именно по-другому)
 
Цитата
Михаил Л написал:
то алгоритм сортировки либо отсутствует, либо алгоритм сортировки - хаос.
Сортировка отсутствующим алгоритмом - это сильно...  :D  То, что вы не знаете, как, еще не значит, что там хаос, + PQ должен сортировать быстро, а не так, как вам удобно.

Например, там сортировка Хоара + слияние, а размер чанка определяется динамически, плюс поправка на необходимость сортировки по нескольким столбцам (многомерная сортировка), и все вместе называется, скажем, "алгоритм Раджапутры Сингха-младшего". Вряд ли вам станет легче.

Вы же хотите, чтобы в любой сортировке было еще одно измерение - номер строки. Это замедлит даже обычную сортировку по одному столбцу. И нафига это надо?
F1 творит чудеса
Страницы: 1
Наверх