Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Dynamic number of rows in a table

Hello,

 

I am trying to build a table in a PowerBI report where each row corresponds to a different date. If the current day is day 1st of the month, I want the table to show all the days of the previous month, from 1st to 31st. If the current day is 2nd, I want the table to show only the row corresponding to the 1st day of the current month.

Then, for every completed day of the current month, one row more should be added to the table (ex: if today is 21st of August, the table should be showing from 1st to 20th of August)

Should I approach this problem with Power Query? Any tip on how to do it?

 

Thanks a lot!

Need Help
  • jennratten's avatar
    jennratten
    3 years ago

    Hello - this will do it.  Please add a new blank query with the script below.  There are three different scenarios that you can use to test the result by commenting (adding two forward slashes) to varToday lines and removing them from one.

    Script

    let 
    // Test various scenarios
    //varToday = Date.From ( DateTime.FixedLocalNow() ),
    varToday = #date ( 2023, 8, 1 ),
    //varToday = #date ( 2023, 8, 2 ),
    Yesterday = Date.AddDays ( varToday, -1 ),
    CurrentMonthStart = Date.StartOfMonth ( varToday ),
    CurrentMonthEnd = Date.EndOfMonth ( varToday ),
    PriorMonthStart = Date.AddMonths ( CurrentMonthStart, -1 ),
    PriorMonthEnd = Date.EndOfMonth ( PriorMonthStart ),
    TodayIsCurrentMonthStart = varToday = CurrentMonthStart,
    minDate = if TodayIsCurrentMonthStart then PriorMonthStart else CurrentMonthStart,
    maxDate = if TodayIsCurrentMonthStart then PriorMonthEnd else Yesterday,
    ListDates = List.Dates(minDate, Number.From(maxDate) - Number.From(minDate) + 1,#duration(1,0,0,0)),
    ListToTable = Table.FromList(ListDates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    ChangeType = Table.TransformColumnTypes(ListToTable,{{"Column1", type date}}),
    RenameDate = Table.RenameColumns(ChangeType,{{"Column1", "Date"}})
in
    RenameDate

     Result for 8/1/23

     

    Result for 8/21/2023

     

    Result for 8/2/2023

     

4 Replies

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

    Hello - this will do it.  Please add a new blank query with the script below.  There are three different scenarios that you can use to test the result by commenting (adding two forward slashes) to varToday lines and removing them from one.

    Script

    let 
    // Test various scenarios
    //varToday = Date.From ( DateTime.FixedLocalNow() ),
    varToday = #date ( 2023, 8, 1 ),
    //varToday = #date ( 2023, 8, 2 ),
    Yesterday = Date.AddDays ( varToday, -1 ),
    CurrentMonthStart = Date.StartOfMonth ( varToday ),
    CurrentMonthEnd = Date.EndOfMonth ( varToday ),
    PriorMonthStart = Date.AddMonths ( CurrentMonthStart, -1 ),
    PriorMonthEnd = Date.EndOfMonth ( PriorMonthStart ),
    TodayIsCurrentMonthStart = varToday = CurrentMonthStart,
    minDate = if TodayIsCurrentMonthStart then PriorMonthStart else CurrentMonthStart,
    maxDate = if TodayIsCurrentMonthStart then PriorMonthEnd else Yesterday,
    ListDates = List.Dates(minDate, Number.From(maxDate) - Number.From(minDate) + 1,#duration(1,0,0,0)),
    ListToTable = Table.FromList(ListDates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    ChangeType = Table.TransformColumnTypes(ListToTable,{{"Column1", type date}}),
    RenameDate = Table.RenameColumns(ChangeType,{{"Column1", "Date"}})
in
    RenameDate

     Result for 8/1/23

     

    Result for 8/21/2023

     

    Result for 8/2/2023

     

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

    Hi

     

    let 
    varToday = #date(2023, 8, 21),
    Yesterday = Date.AddDays ( varToday, -1 ),
    YesterdayMonthStart = Date.StartOfMonth(Yesterday),
    Result = Table.FromColumns(
        {List.Dates(YesterdayMonthStart, Duration.Days(Yesterday-YesterdayMonthStart)+1, #duration(1,0,0,0))},
        type table [Date = date])
    in
    Result

     

    Stéphane