cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
harieee
Frequent Visitor

Join Calender table with Main Table using 2 Date Columns

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?

1 ACCEPTED SOLUTION
edhans
Super User III
Super User III

How would you want this to look? In your example @harieee 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.

edhans_0-1618940370792.png

 

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

3 REPLIES 3
edhans
Super User III
Super User III

How would you want this to look? In your example @harieee 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.

edhans_0-1618940370792.png

 

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

harieee
Frequent Visitor

Hi edhans,

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

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors