Divide monthly table to a daily table

Hi Anonymous ,

To convert your table into a basic scalar output, you can follow these steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WioiMindxVdJRMjIwMjIwVIrVgYj5hcHEjOBiocEwMWO4mLMfTMwELObo5IxhHkgM3TyQGLp5IDEU82IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Company = _t, Period = _t]),
    
    addPeriodStart =
    Table.AddColumn(Source, "periodStart", each
        #date(
            Number.From(Text.Start([Period], 4)),
            Number.From(Text.End([Period], 2)),
            01
        )
    ),
    addDaysInMonth = Table.AddColumn(addPeriodStart, "daysInMonth", each Date.DaysInMonth([periodStart])),
    addCustomCalc = List.Sum(addDaysInMonth[daysInMonth]) / Table.RowCount(addDaysInMonth)
in
    addCustomCalc

It outputs a scalar value of 30.

To actually expand your table into dated rows, you can follow these steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WioiMindxVdJRMjIwMjIwVIrVgYj5hcHEjOBiocEwMWO4mLMfTMwELObo5IxhHkgM3TyQGLp5IDEU82IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Company = _t, Period = _t]),
    
    addPeriodStart =
    Table.AddColumn(Source, "periodStart", each
        #date(
            Number.From(Text.Start([Period], 4)),
            Number.From(Text.End([Period], 2)),
            01
        )
    ),
    addListGen = Table.AddColumn(addPeriodStart, "listGen", each List.Transform(
    {Number.From([periodStart])..Number.From(Date.EndOfMonth([periodStart]))},
    each Date.From(_)
)),
    exapndListGen = Table.ExpandListColumn(addListGen, "listGen")
in
    exapndListGen

Which outputs this:

Pete