Join Calender table with Main Table using 2 Date Columns

[Anonymous]

Hi

  I have a table where i have billions of records and 2 date columns (Start & End Date). I need to join this table with calender table to get the single calender date for slice and dice the data instead of using 2 date columns. 

Also, i need to apply incremental refresh on top of the single calender date column. I have tried in SQL query level which causing query skew in Teradata.

Example

Start Date - 1/1/2020

End Date - 1/1/9999

How to handle this case?

[edhans · Accepted solution]

How would you want this to look? In your example @Anonymous you show a Jan 1 2020 start date and a Jan 1, 9999 end date. What is the "single" date you need?

If you want a single date column, you can definitely do that. By adding this formula to a custom column you will get a list of values that can be converted to a date:

{Number.From([Start Date])..Number.From([End Date])}

But in your date examples, that will be over 2.7M records going out to the year 9999.

It would look like this. You would just remove start/end date and rename Date Range to your Date column to join to the calendar table with.

Full code here.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJQ0gEzLYFAKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Start Date" = _t, #"End Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"End Date", type date}, {"Start Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Date Range", each {Number.From([Start Date])..Number.From([End Date])}),
    #"Expanded Date Range" = Table.ExpandListColumn(#"Added Custom", "Date Range"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Date Range",{{"Date Range", type date}})
in
    #"Changed Type1"

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

If that isn't what you need, please be more speific on the expected output.

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

[Anonymous]

Hi edhans,

   is it possible to configure a incremental refresh with this 2 start Date and End Column? 

[edhans]

One or the other, but not both. But yes, as long as this is in a data source that supports Incremental Referesh, you can pick the date field for it to look at.