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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Table with all dates in an interval

I have a table with this example data:

 

SolicitationIDAssetOpen_atClosed_at
REQ0001BKN00012022/01/202022/01/25

 

I need to generate a table with date range:

 

Solicitation IDAssetDate
REQ0001BKN00012022/01/20
REQ0001BKN00012022/01/21
REQ0001BKN00012022/01/22
REQ0001BKN00012022/01/23
REQ0001BKN00012022/01/24
REQ0001BKN00012022/01/25

 

Help?

1 ACCEPTED SOLUTION
ValtteriN
Super User
Super User

Hi,

You can do this in PQ like this:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs7PyUzOLEksyczPU9JR8nRxLC5OLQGy/AtS8+ITQSznnPzi1BQQO1YnWinINdDAwMAQKO7k7QdlGRkYGekbGOobGSBzTJViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Solicitation", type text}, {"IDAsset", type text}, {"Open_at", type date}, {"Closed_at", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "NewDate", each {Number.From([Open_at])..Number.From([Closed_at])}),
#"Expanded NewDate" = Table.ExpandListColumn(#"Added Custom", "NewDate"),
#"Changed Type2" = Table.TransformColumnTypes(#"Expanded NewDate",{{"NewDate", type date}})
in
#"Changed Type2"

The bolded steps are what you want. Also I recommend cheking out this video from Curbal explaining the logic behind this:
https://www.youtube.com/watch?v=u3HLseZxSj4


I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

5 REPLIES 5
Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

 

Picture3.png

 

New Table =
VAR _calendartable =
SELECTCOLUMNS ( CALENDARAUTO (), "@date", [Date] )
VAR _newtable =
GENERATE (
Data,
FILTER (
_calendartable,
VAR _mindate =
SELECTEDVALUE ( Data[Open_at] )
VAR _maxdate =
SELECTEDVALUE ( Data[Closed_at] )
RETURN
[@date] >= _mindate
&& [@date] <= _maxdate
)
)
RETURN
SUMMARIZE ( _newtable, Data[Solicitation], Data[IDAsset], [@date] )


If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.
Anonymous
Not applicable

@Jihwan_Kim good morning! Thank you for your answer!

The soluction works well for a little data, I couldn't make it work with a big data (30M rows, for example). 

ValtteriN
Super User
Super User

Hi,

You can do this in PQ like this:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs7PyUzOLEksyczPU9JR8nRxLC5OLQGy/AtS8+ITQSznnPzi1BQQO1YnWinINdDAwMAQKO7k7QdlGRkYGekbGOobGSBzTJViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Solicitation", type text}, {"IDAsset", type text}, {"Open_at", type date}, {"Closed_at", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "NewDate", each {Number.From([Open_at])..Number.From([Closed_at])}),
#"Expanded NewDate" = Table.ExpandListColumn(#"Added Custom", "NewDate"),
#"Changed Type2" = Table.TransformColumnTypes(#"Expanded NewDate",{{"NewDate", type date}})
in
#"Changed Type2"

The bolded steps are what you want. Also I recommend cheking out this video from Curbal explaining the logic behind this:
https://www.youtube.com/watch?v=u3HLseZxSj4


I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

Good morning @ValtteriN! Thank you so much for your answer, I can resolve my problem with your example!!!

 

My code:

 

PHELDMAN_0-1643285842548.png

let
Source = Table.SelectRows(maint_reqs, each ([status] <> "Cancelada" and [status] <> "Reprovado" and [Categoria] = "EQUIPAMENTOS")),
#"Added Custom0" = Table.AddColumn(Source, "executed_at_solved", each if[executed_at] is null then DateTime.LocalNow() else [executed_at]),
#"Select Columns" = Table.SelectColumns(#"Added Custom0", {"reqNum-SS", "BKNativo", "opened_at", "executed_at_solved"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Select Columns", {{"reqNum-SS", type text}, {"BKNativo", type text}, {"opened_at", type date}, {"executed_at_solved", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "Date", each {Number.From([opened_at])..Number.From([executed_at_solved])}),
#"Expanded NewDate" = Table.ExpandListColumn(#"Added Custom", "Date"),
#"Changed Type2" = Table.TransformColumnTypes(#"Expanded NewDate",{{"Date", type date}})
in
#"Changed Type2"

Greg_Deckler
Community Champion
Community Champion

@Anonymous You can use GENERATE and CALENDAR like:

Table1 =
  VAR __Table = 'Table'
  VAR __Table1 = GENERATE(__Table, CALENDAR([Open_at], [Closed_at]))
RETURN
  __Table1


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

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