Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
Anonymous
Not applicable

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!

1 ACCEPTED SOLUTION
jennratten
Super User
Super User

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

jennratten_0-1692621927476.png

 

Result for 8/21/2023

jennratten_1-1692621971192.png

 

Result for 8/2/2023

jennratten_2-1692621995771.png

 

View solution in original post

4 REPLIES 4
slorin
Super User
Super User

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 

jennratten
Super User
Super User

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

jennratten_0-1692621927476.png

 

Result for 8/21/2023

jennratten_1-1692621971192.png

 

Result for 8/2/2023

jennratten_2-1692621995771.png

 

Anonymous
Not applicable

Thanks @jennratten ! That was perfect

You're welcome!!

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

October Power BI Update Carousel

Power BI Monthly Update - October 2025

Check out the October 2025 Power BI update to learn about new features.

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

Top Kudoed Authors