Страницы: 1
RSS
PQ: автоматическое добавление колонки при обновлении данных?
 
Добрый день. Прошу натолкнуть на мысль или предложить решение.
Каждый раз при изменении цен поставщик присылает мне новый прайс. Все его прайсы я складирую в отдельную папку, добавляя к файлу префикс (дату получения).
При помощи Power Query хочу сделать сводный файл, в котором бы каждый раз при его обновлении автоматом добавлялся столбец с новыми ценами, если они обнаруживаются в очередном добавленном в каталог файле от поставщика. Естественно, список номенклатуры во всех файлах постоянный и неизменный. А так же хотелось бы в шапке нового добавленного столбца иметь дату, вычлененную из названия файла.
Буду признателен за помощь.
Изменено: Partizano _pl - 25.02.2022 13:58:31
 
Partizano _pl, файл пример и пару-тройку примеров прайсов за Вас составить? :)
 
Цитата
написал:
Partizano _pl , файл пример и пару-тройку примеров прайсов за Вас составить?
Я не совсем понимаю как можно составить пример для файла с запросами PQ, ведь пути к фалам будут разные на разных ПК. Но попробовал сделать (во вложении)
 
Partizano _pl, вариант:
Код
let
  src = Folder.Files ( "C:\Users\kovalev.i\Downloads\P_Excel\P_Excel" ),
  filter = Table.SelectRows ( src, each Text.StartsWith ( [Name], "Прайс_" ) and [Extension] = ".xlsx" ),
  addDate = Table.AddColumn ( filter, "Дата", each Text.BetweenDelimiters ( [Name], "Прайс_", ".xlsx" ), type text ),
  needClmns = Table.SelectColumns ( addDate, { "Content", "Дата" } ),
  transform = Table.TransformColumns (
    needClmns,
    {
      {
        "Content",
        ( x ) =>
          [
            data      = Excel.Workbook ( x ){[ Item = "Лист1", Kind = "Sheet" ]}[Data],
            promHeads = Table.PromoteHeaders ( data, [ PromoteAllScalars = true ] )
          ][promHeads]
      }
    }
  ),
  expand = Table.ExpandTableColumn ( transform, "Content", Table.ColumnNames ( transform[Content]{0} ) ),
  pivot = Table.Pivot ( expand, List.Distinct ( expand[Дата] ), "Дата", "Цена", List.Sum ),
  tblToClmns = List.Buffer ( Table.ToColumns ( pivot ) ),
  clmnsName = List.Buffer ( Table.ColumnNames ( pivot ) ),
  needDel = List.Accumulate (
    { 1 .. List.Count ( tblToClmns ) - 1 },
    {},
    ( s, c ) => if tblToClmns{c} = tblToClmns{c - 1} then s & { clmnsName{c} } else s
  ),
  result = Table.RemoveColumns ( pivot, needDel )
in
  result
 
Вариант пошустрее. Попробуйте на реальных данных с большим количеством товаров:
Код
let
  src = Folder.Files ( "C:\Users\kovalev.i\Downloads\P_Excel\P_Excel" ),
  filter = Table.SelectRows ( src, each Text.StartsWith ( [Name], "Прайс_" ) and [Extension] = ".xlsx" ),
  addDate = Table.AddColumn ( filter, "Дата", each Text.BetweenDelimiters ( [Name], "Прайс_", ".xlsx" ), type text ),
  needClmns = Table.SelectColumns ( addDate, { "Content", "Дата" } ),
  transform = Table.TransformColumns (
    needClmns,
    {
      {
        "Content",
        ( x ) =>
          [
            data      = Excel.Workbook ( x ){[ Item = "Лист1", Kind = "Sheet" ]}[Data],
            promHeads = Table.PromoteHeaders ( data, [ PromoteAllScalars = true ] )
          ][promHeads]
      }
    }
  ),
  expand = Table.ExpandTableColumn ( transform, "Content", Table.ColumnNames ( transform[Content]{0} ) ),
  pivot = Table.Pivot ( expand, List.Distinct ( expand[Наименование] ), "Наименование", "Цена", List.Sum ),
  productClmns = List.Buffer ( List.RemoveItems ( Table.ColumnNames ( pivot ), { "Дата" } ) ),
  delDup = Table.Group ( pivot, productClmns, { { "Дата", ( tbl ) => tbl[Дата]{0} } }, GroupKind.Local ),
  reorder = Table.ReorderColumns ( delDup, { "Дата" } & productClmns ),
  demHeads = Table.DemoteHeaders ( reorder ),
  transpose = Table.Transpose ( demHeads ),
  promHeads = Table.PromoteHeaders ( transpose, [ PromoteAllScalars = true ] )
in
  promHeads
Изменено: surkenny - 26.02.2022 11:11:44
 
Цитата
написал:
Вариант пошустрее. Попробуйте на реальных данных с большим количеством товаров:
Спасибо большое!
Ваш вариант вполне рабочий. Мне осталось только подправить под реальные прайсы, чем сейчас и займусь.
Как такое сложное вообще написать возможно, у меня в голове не укладывается )
А можете перевести на человеческий что происходит в процедуре transform? А то я на ней подзастрял немного, адаптируя скрипт под реальный прайс, в котором все выглядит немного иначе, чем в примере.
 
Partizano _pl, изменяется содержимое каждой строки записи Content. В Content находится книга excel. Далее двумя шагами мы извлекаем данные. Первый шаг data - открываем книгу, выбираем содержимое Лист1. Второй шаг - повышаем заголовки. Вы это можете сделать руками, а потом вставить в мой код:
Код
 transform = Table.TransformColumns (
    needClmns,
    {
      {
        "Content",
        ( x ) =>
          let 
              a = …,
              b = …,
              c = …
          in c
      }
    }
  )

Конструкция [a=1, b=2, c=a*b][c] аналогична let a=1, b=2, c=a*b in c
Страницы: 1
Наверх