Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Divide monthly table to a daily table

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

CompanyPeriod
XYZ_DE202201
XYZ_NV202202
XYZ_US202203
XYZ_CN202204
ABC_DE202201
ABC_NV202202
ABC_US202203
ABC_CN202204

 

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

 

Need Help
  • BA_Pete's avatar
    BA_Pete
    3 years ago

    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

2 Replies

  • BA_Pete's avatar
    BA_Pete
    Icon for Super User rankSuper User
    3 years ago

    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

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Icon for Community Champion rankCommunity Champion
    3 years ago

    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