How to generate explode the datediff between 2 dates to multiple rows

[balak]

I have 2 columns , startdate and processeddate and I want to explore the datediff between these 2 dates   to muliptle rows like below in Dax and i am assuming this will be separate table and need to define the relation ship to the startdate in the base dataset and new table in the pbi report to able to plot the Days(see second table) on x-axis

source

StartDate     endDate  DaysSinceStartdate
10/15/2021 10/20/2021   5

need to expand the DaysSinceStartDate ( which is datediff of startDate and endDate) as below

stardate     enddate      Days
10/15/2021 10/20/2021 Day1
10/15/2021 10/20/2021 Day2
10/15/2021 10/20/2021 Day3
10/15/2021 10/20/2021 Day 4
10/15/2021 10/20/2021 Day 5

Any help on pointers on how to achieve this is  appreciated

[Ashish_Mathur]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Duration", each 1*([endDate]-[StartDate])),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Duration", Int64.Type}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Custom", each {1..[Duration]}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom1", "Custom"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Duration"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"StartDate", type date}, {"endDate", type date}})
in
    #"Changed Type1"

Hope this helps.

[PaginatedDino]

@Ashish_Mathur  Thanks this worked for me with but with an integer range use case. It was a small look up table that eventually results in 20.000 exploded rows, so not a very huge table. Performance is ok. 

[Ashish_Mathur]

Glad to know.  Thank you.

[AlexisOlson]

If you do this in the query editor, you can create a list from 1 to DaysSinceStartDat, expand to new rows, and prepend "Day" to that number.

M Code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTQNzTVNzIwMlTSAXGMDCCc2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [StartDate = _t, EndDate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"StartDate", type date}, {"EndDate", type date}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "DaysSinceStartDate", each Duration.Days([EndDate]-[StartDate]), Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Custom1", "Days", each {1..[DaysSinceStartDate]}, type list),
    #"Expanded Days" = Table.ExpandListColumn(#"Added Custom", "Days"),
    #"Added Prefix" = Table.TransformColumns(#"Expanded Days", {{"Days", each "Day " & Text.From(_, "en-US"), type text}})
in
    #"Added Prefix"

If you need to do this in DAX, then you can define a calculated table like this

SELECTCOLUMNS (
    GENERATE ( Table1, GENERATESERIES ( 1, [DaysSinceStartDate] ) ),
    "StartDate", [StartDate],
    "EndDate", [EndDate],
    "Days", "Days " & [Value]
)

[balak]

Thank much @AlexisOlson ,

1) regarding the Dax approach,  I have the "DayssinceStartDate" as part of the dataset and Dax is not recognizing this field, in the,  GENERATESERIES ( 1, [DaysSinceStartDate] ) ) ? unless its part of aggregate function

2) regarding the approach1 (custom), is this going to be  new dataset and how to refer to the source dataset assuming its 'table1' and has the fields, startdate,enddate and dayssinceStartDate ? 

3)Assumimg I am going with the dax way, need to have the startDate columm from table1 along with DaysSinceStartdate to be able to define relationship to other table which has just the startDate (single  value)  

End goal is I need to able to plot the DaysSinceStartDate on the x-axis of chart with the counts from the table1 which has the startdate and enddate fields

[AlexisOlson]

It gives me an angry red underline too but it does work. I think this is probably a bug in the IntelliSense.

I'm sorry but I don't quite follow what you're getting at in 2 and 3.