How to create start and end date columns from date column in Power BI?

Question

I need to make the start date and end date columns from the date column. I have a table of employees and the dates on which they are on the vacation but I need to reduce the table so that there is no separate row for each date if that date is part of a vacation that lasts more than one day. But care should be taken that one person can be more than one period per year on vacation and that two people or more can be on the same period on vacation. How to do it?

Example Input:

Name	Date
Ivana	12/25/2020
Ivana	12/26/2020
Ivana	12/27/2020
Ivana	4/20/2020
Ivana	4/21/2020
Jelena	4/20/2020
Jelena	4/21/2020

Expected Output:

Name	Start Date	EndDate
Ivana	12/25/2020	12/27/2020
Ivana	4/20/2020	4/21/2020
Jelena	4/20/2020	4/21/2020

score 1 · Answer 1 · answered Dec 14 '20 at 16:49

1

I can't think of a very simple way to do this. The hard part is breaking up the date column into contiguous sections. I've defined a custom function for this:

(L as list) as table =>
let
    N = List.Count(L),
    Gen = List.Generate(
        () => [i = 0, j = 0],
        each [j] < N,
        each [i = if (try L{[j] + 1} otherwise L{[j]}) = Date.AddDays(L{[j]}, 1)
                  then [i] else [i] + 1,
              j = [j] + 1],
        each [i]
    ),
    ToTable = Table.FromColumns({L, Gen}, {"Date", "Index"})
in
    ToTable

This takes a list of dates and creates a distinct index for each set of consecutive days.

For example the list {4/20/2020, 4/21/2020, 12/25/2020, 12/26/2020, 12/27,2020} returns

      Date | Index
-----------+-------
 4/20/2020 | 0
 4/21/2020 | 0
12/25/2020 | 1
12/26/2020 | 1
12/27/2020 | 1

The idea is to use this function to group the dates for each separate Name.

Once we have that, we can expand the tables and group on Name and Index taking the min and max over Date to get the start and end dates.

The full M code I used can be pasted into the Advanced Editor (the function above is defined separately):

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8ixLzEtU0lEyNNI3MtU3MjAyUIrVQRU2wy5sjilsAhTCKmqIEPVKzUnFohhZGKY6FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Name"}, {{"DateIndex", each DateGroupIndex([Date]), type table}}),
    #"Expanded DateIndex" = Table.ExpandTableColumn(#"Grouped Rows", "DateIndex", {"Date", "Index"}, {"Date", "Index"}),
    #"Grouped Rows1" = Table.Group(#"Expanded DateIndex", {"Name", "Index"}, {{"Start Date", each List.Min([Date]), type date}, {"End Date", each List.Max([Date]), type date}})
in
    #"Grouped Rows1"

answered Dec 14 '20 at 16:49

Alexis Olson

38,724
7
42
64

I tried putting those three last steps into my query but there is an error saying: Expression.Error: The name 'DateGroupIndex' wasn't recognized. Make sure it's spelled correctly. – Ivana Lovrinović Dec 15 '20 at 16:50
Does *DateGroupIndex* show up as a function like in my first screenshot? You need to define the function to be able to use it. – Alexis Olson Dec 15 '20 at 17:03
Sorry, where do I have to create a function? – Ivana Lovrinović Dec 17 '20 at 09:11
Sorry, are you free for zoom meeting? I can't do this – Ivana Lovrinović Dec 17 '20 at 09:17
No zoom. To define a function, create a separate query (a blank query is fine), paste the code I specified into the Advanced Editor, and make sure the query is named `DateGroupIndex`. – Alexis Olson Dec 17 '20 at 14:47
The problem is that my other columns disappear – Ivana Lovrinović Dec 18 '20 at 13:36
What other columns? – Alexis Olson Dec 18 '20 at 14:42
Other columns in my table, I don't have just name and date – Ivana Lovrinović Dec 18 '20 at 15:01
Group or aggregate on those other columns as you’d like. I can’t really answer something that you never specified in the question. – Alexis Olson Dec 18 '20 at 15:17
I tried putting those columns in this step: #"Grouped Rows" after {"Name"} for example {"email"} but it doesn't work – Ivana Lovrinović Dec 18 '20 at 15:42
Like this? {“Name”, “Index”, “email”} – Alexis Olson Dec 18 '20 at 15:44
In which step do I have to write those extra columns ? Grouped Rows, Expanded DateIndex or Grouped Rows1? – Ivana Lovrinović Dec 18 '20 at 16:00
I managed to do that but it doesn't work. https://drive.google.com/file/d/1yhcis-QVQkdwWfu7VR9s692_erTcFRYm/view?usp=sharing – Ivana Lovrinović Dec 18 '20 at 16:14

How to create start and end date columns from date column in Power BI?

1 Answers1