let
src = Excel.CurrentWorkbook(){[ Name = "data" ]}[Content],
addDate = Table.AddColumn ( src, "Name", each Text.From ( Date.FromText ( "01 " & [Месяц], "RU-ru" ) ), type text ),
rename = Table.RenameColumns ( addDate, { { "Кол-во пирожков", "Value" } } ),
data = Record.FromTable ( rename ),
dates = List.Transform ( Record.FieldNames ( data ), Date.From ),
dateFrom = Date.StartOfMonth ( List.Min ( dates ) ),
dateTo = Date.EndOfMonth ( List.Max ( dates ) ),
generate = Table.FromColumns (
{ List.Dates ( dateFrom, Duration.TotalDays ( dateTo - dateFrom ) + 1, #duration ( 1, 0, 0, 0 ) ) },
type table [ Дата = Date.Type ]
),
addY = Table.AddColumn ( generate, "Год", each Date.Year ( [Дата] ), Int64.Type ),
addQ = Table.AddColumn ( addY, "Квартал", each Date.QuarterOfYear ( [Дата] ), Int64.Type ),
addW = Table.AddColumn ( addQ, "Неделя года", each Date.WeekOfYear ( [Дата] ), Int64.Type ),
addMN = Table.AddColumn ( addW, "Месяц", each Date.MonthName ( [Дата] ) ),
addDinM = Table.AddColumn (
addMN,
"Пирожков в день",
each Record.FieldOrDefault ( data, Text.From ( Date.StartOfMonth ( [Дата] ) ), null ) / Date.DaysInMonth ( [Дата] ),
type number
)
in
addDinM |