Divide monthly table to a daily table

[cho]

I have a table like this and this is a very small sample:

Company	Period
XYZ_DE	202201
XYZ_NV	202202
XYZ_US	202203
XYZ_CN	202204
ABC_DE	202201
ABC_NV	202202
ABC_US	202203
ABC_CN	202204

Basically in this case, since there are 8 companies with 4 months, I need a table with row count:

(#of days in Jan + #of days in Feb + #of days in Mar + #of days in Apr) * #of companies

[wdx223_Daniel]

Source is your original table

NewStep= let a=List.Distinct(Source[Company]),b=List.Sort(List.Distinct(Source[Period])),c=List.Sum(List.Transform(b,each Date.DaysInMonth(Date.From(_&"01")))) in #table({"Company","Date"},List.TransformMany(a,each List.Dates(Date.From(b{0}&"01"),c,Duration.From(1)),(x,y)=>{x,y}))

this gives you a table with 960 rows

[BA_Pete · Accepted solution]

Hi @cho ,

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