Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
В Power Query создать столбец со значениями за предыдущий год
 
#1
20.11.2021 14:32:07
Добрый день!

Подскажите пожалуйста у меня есть столбец со значениями выручки по датам, как в Power Query рядом создать столбец со значениями выручки за предыдущий год?
 
 
 
#2
20.11.2021 18:57:08
Надежда A, непонятно, что Вам нужно. Сравниваете месяцы? Даты? Если у Вас таблица продаж, то на каждую дату может быть много продаж, для каждой строки что вывести?
Предполагаю, что нужны сумма продаж каждого месяца и сумма продаж за этот же месяц в предыдущем году.

P.S. Надежда A, вот Вы сами перечитайте своё сообщение, Вам все понятно без файла-примера?
Изменено: surkenny - 20.11.2021 18:59:30
 
 
 
#3
21.11.2021 11:53:22
Смотрите приложение с кодами
2020
Код
let
    Source = Excel.Workbook(File.Contents("C:\Users\seven\Documents\Documents\выручка_1.xlsx"), null, true),
    #"2020_Sheet" = Source{[Item="2020",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"2020_Sheet", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"дата", type date}, {"выручка2021", Int64.Type}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "дата", "дата - Copy"),
    #"Extracted Month" = Table.TransformColumns(#"Duplicated Column",{{"дата - Copy", Date.Month}}),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Extracted Month", "дата", "дата - Copy.1"),
    #"Extracted Day" = Table.TransformColumns(#"Duplicated Column1",{{"дата - Copy.1", Date.Day}})
in
    #"Extracted Day"
2021
Код
let
    Source = Excel.Workbook(File.Contents("C:\Users\seven\Documents\Documents\выручка_1.xlsx"), null, true),
    #"2021_Sheet" = Source{[Item="2021",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"2021_Sheet", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"дата", type date}, {"выручка2021", Int64.Type}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "дата", "дата - Copy"),
    #"Extracted Month" = Table.TransformColumns(#"Duplicated Column",{{"дата - Copy", Date.Month}}),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Extracted Month", "дата", "дата - Copy.1"),
    #"Extracted Day" = Table.TransformColumns(#"Duplicated Column1",{{"дата - Copy.1", Date.Day}}),
    #"Merged Queries" = Table.NestedJoin(#"Extracted Day",{"дата - Copy", "дата - Copy.1"},#"2020 (2)",{"дата - Copy", "дата - Copy.1"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"выручка2021"}, {"NewColumn.выручка2021"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded NewColumn",{{"NewColumn.выручка2021", "выручка2020"}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns",{"выручка2020"})
in
    #"Removed Other Columns"
 
 
 
#4
21.11.2021 12:36:55
jakim, а куда Вы дели продажи за 29 февраля 2020? :)
Для Вашего примера (в качестве данных брал таблицу сразу за 2020 и 2021, работать будет на любом числе лет) код. Для 28 февраля 2021 сумма за прошлый год - это сумма за 28 и 29 февраля.
Код
let
  Источник   = Excel.CurrentWorkbook(){[ Name = "sales" ]}[Content],
  typed      = Table.TransformColumnTypes ( Источник, { { "дата", type date }, { "выручка", type number } } ),
  group      = Table.Buffer ( Table.Group ( typed, { "дата" }, { { "выручка", each List.Sum ( [выручка] ), type nullable number } } ) ),
  transform  = Table.TransformColumns ( group, { "дата", ( x ) => Text.From ( Date.AddYears ( x, 1 ) ), type text } ),
  groupLastY = Table.Group ( transform, { "дата" }, { { "выручка", each List.Sum ( [выручка] ), type nullable number } } ),
  rename     = Table.RenameColumns ( groupLastY, { { "дата", "Name" }, { "выручка", "Value" } } ),
  lastYRec   = Record.FromTable ( rename ),
  result     = Table.AddColumn ( group, "выручка_Прошлый год", each Record.FieldOrDefault ( lastYRec, Text.From ( [дата] ), null ), type number )
in
  result

Если нужно еще будет по товарам/клиентам разбивать, то join, скорее всего, будет помедленнее.
Но суть в том, что сумма за месяц прошлого года будет корректна только тогда, когда в продажах текущего есть ВСЕ даты (вернее все те, что были в прошлом году).
Поэтому такую аналитику проще в PP делать: CALCULATE ( SUM ( sales[выручка] ), SAMEPERIODLASTYEAR ( sales[дата] ) )
Изменено: surkenny - 21.11.2021 12:48:17
 
 
 
#5
21.11.2021 13:13:34
Добавил таблицу дат без пропусков. Код будет работать корректно, даже если на какие-то даты нет данных продаж:
Код
let
  src = Excel.CurrentWorkbook(){[ Name = "sales" ]}[Content], 
  typed = Table.TransformColumnTypes ( src, { { "дата", type date }, { "выручка", type number } } ), 
  group = Table.Buffer ( Table.Group ( typed, { "дата" }, { { "выручка", each List.Sum ( [выручка] ), type nullable number } } ) ), 
  dateFrom = List.Min ( group[дата] ), 
  dateTo = List.Max ( group[дата] ), 
  dates = Table.FromColumns (
    { List.Dates ( dateFrom, Duration.TotalDays ( dateTo - dateFrom ) + 1, #duration ( 1, 0, 0, 0 ) ) }, 
    type table [ дата = Date.Type ]
  ), 
  transformCurY = Table.TransformColumns ( group, { "дата", ( x ) => Text.From ( x ), type text } ), 
  renameCurY = Table.RenameColumns ( transformCurY, { { "дата", "Name" }, { "выручка", "Value" } } ), 
  curYRec = Record.FromTable ( renameCurY ), 
  transformLastY = Table.TransformColumns ( group, { "дата", ( x ) => Text.From ( Date.AddYears ( x, 1 ) ), type text } ), 
  groupLastY = Table.Group ( transformLastY, { "дата" }, { { "выручка", each List.Sum ( [выручка] ), type nullable number } } ), 
  renameLastY = Table.RenameColumns ( groupLastY, { { "дата", "Name" }, { "выручка", "Value" } } ), 
  lastYRec = Record.FromTable ( renameLastY ), 
  addCurYSales = Table.AddColumn (
    dates, 
    "выручка", 
    each Record.FieldOrDefault ( curYRec, Text.From ( [дата] ), null ), 
    type number
  ), 
  addLastYSales = Table.AddColumn (
    addCurYSales, 
    "выручка_Прошлый год", 
    each Record.FieldOrDefault ( lastYRec, Text.From ( [дата] ), null ), 
    type number
  )
in
  addLastYSales
 
 
 
#6
21.11.2021 15:56:01
Супер!!! Спасибо огромное)))
 
 
 
#7
22.11.2021 15:42:51
Добрый день!
Помогите пожалуйста еще. Все-таки не могу понять как добавить разбивку по товарам.
Вы написали использовать join, но в какой момент?
 
 
 
#8
22.11.2021 15:54:25
Цитата
Надежда A написал:
Вы написали использовать join
перечитал и понял, что двояко можно понять :) Я имел ввиду, что способ, используемый jakim, скорее всего, будет медленнее моего и использование NestedJoin приведет к долгому выполнению :) . Но это еще и от объема данных зависит.
Я пример за Вас (пример данных и нужного результата в файле excel) составлять не буду.

P.S. А использование PQ - это Ваша инициатива или потребность? Как я и писал, в PP проще (и сможете там группировать, как захотите, не меняя меру): CALCULATE ( SUM ( sales[выручка] ), SAMEPERIODLASTYEAR ( sales[дата] ) )
Изменено: surkenny - 22.11.2021 15:56:06
 
 
 
#9
22.11.2021 16:10:21
Я использую PQ, потому что мне нужно, чтобы был один столбец со значениями, а показатели Выручка и Выручка прошлого года были в строках. В PQ я это сделаю потом анпивотом.
Если подскажите как это сделать в PP - буду очень благодарна
 
 
 
#10
22.11.2021 16:16:10
Надежда A, третий раз: без примера не подскажу:)
 
 
 
#11
22.11.2021 16:33:14
Посмотрите пожалуйста, подойдет такой файл?
Характеристика1, характеристика2, я имела ввиду какие-то доп характеристики, цвет, размер
Изменено: Надежда A - 22.11.2021 16:34:52
 
 
 
#12
22.11.2021 17:36:25
Надежда A, я бы так сделал (во вложении). Но значения я бы все-так в столбцы поместил:)
 
 
 
#13
22.11.2021 22:51:06
Получается вы все-таки в PP сделали? Мне нужно в PQ. У меня все замечательно работает по вашему коду для PQ, что вы ранее писали, но туда нужно добавить разбивку по товарам. Это возможно?
 
 
 
#14
22.11.2021 22:55:29
Надежда A,  так а есть разница?) Так гораздо проще. И группировать можно на лету (по товару, общие продажи, по клиентам и тд, и тп)
 
 
 
Страницы: 1
Наверх