Forum Discussion

balak's avatar
balak
Icon for Microsoft Employee rankMicrosoft Employee
4 years ago

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

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 ...
Need Help
Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
4 years ago

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's avatar
PaginatedDino
Icon for Helper I rankHelper I
3 years ago

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's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    3 years ago

    Glad to know.  Thank you.