How to work with time intervals (Ex : 12/08/2019 - 12/21/2019)

[Anonymous]

Hello!

A manager asked me to create a dashboard with his numbers. However, the date column are actually intervals (See table)

Do you know how I should proceed to be able to link his date column with a calendar?

DATE

12/08/2019 - 12/21/2019

12/22/2019 - 01/04/2020

01/05 - 01/18/2020

01/19 - 02/01/2020

02/02 - 02/15/2020

02/16 -02/29/2020

03/01 - 03/14/2020

03/15 - 03/28/2020

03/29 - 04/11/2020

04/12 -04/25/2020

04/26 -05/09/2020

05/10 -05/23/2020

05/24 -06/06/2020

06/07 -06/20/2020

06/21 -07/04/2020

07/05 -07/18/2020

07/19 -08/01/2020

08/02 -08/15/2020

08/16 -08/29/2020

08/30 -09/12/2020

09/13 -09/26/2020

09/27 -10/10/2020

10/11 -10/24/2020

10/25 -11/07/2020

[AlB]

Hi @Anonymous 

You could do something like this in PQ to pair the periods to their respective dates. Place the following M code in a blank query to see the steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc5BCsAgDETRq4jrlmTGWvQsxftfozHFEpefB5k8TwZFm1DR05ksCI88DjdymUL0sqC6zayen6Fthv6bDSCYJYOhboY7GHuwss5MK4L1y3gB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Period = _t]),
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Period", Splitter.SplitTextByDelimiter(" - ", QuoteStyle.Csv), {"Period.1", "Period.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Period.1", type date}, {"Period.2", type date}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Period number", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Date", each List.Dates([Period.1], Duration.Days([Period.2]-[Period.1]), #duration(1,0,0,0))),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Date"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Period.1", "Period.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Date", type date}})
in
    #"Changed Type1"

Then create a relationship between this table [Date] and you the [Date] column in your standard date table. You can then use "Period number" in a slicer for instance to selecte the period 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[camargos88 · Accepted solution]

@Anonymous ,

Try this m code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VZHRDYQwDENXQXwfSuK2tMyC2H+Nc1Ld0UhIkV9Kbcx97wbRIVC7tmOjgIXYn0/sgN9OTbRSQGPnsk1sI+F5mtfagikxsbWE7dwODlwLLXzXDxexmrC1iTESRjhWsdWRko4euSUKN2yiq2ET06AoiaKSnsLnpZQ9KDRRMLL23FGPjjhSRT0qYuupoRENcaSCxixo5IKGFM97if+hP6UsQXEmCuY15Se+1KUFRU0UzMsamXHS5ws=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DATE = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DATE", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "DATE", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"DATE.1", "DATE.2"}),
    #"Added Custom" = Table.AddColumn(#"Split Column by Delimiter", "Custom", each List.Dates(Date.FromText([DATE.1], "en-us"),
Duration.TotalDays(
Date.FromText([DATE.2], "en-us") - Date.FromText([DATE.1], "en-us")) + 1, #duration(1,0,0,0))),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}})
in
    #"Changed Type1"

you may need to adjust the date format.