Make continuous ranges from date list

[Raaassotto]

I need a query that makes continuous ranges from date list for every machine. We have to find start of every working range for every machine. Every missing date for machine brakes the date range

The input table looks the following way 

Machine	Date	Output
Machine 1	01.05.2025	20
Machine 1	02.05.2025	30
Machine 1	03.05.2025	15
Machine 1	06.05.2025	35
Machine 1	07.05.2025	45
Machine 1	08.05.2025	60

The output table must look this way

Machine	Date	Output	Range Start	Range End
Machine 1	01.05.2025	20	01.05.2025	03.05.2025
Machine 1	02.05.2025	30	01.05.2025	03.05.2025
Machine 1	03.05.2025	15	01.05.2025	03.05.2025
Machine 1	06.05.2025	35	06.05.2025	08.05.2025
Machine 1	07.05.2025	45	06.05.2025	08.05.2025
Machine 1	08.05.2025	60	06.05.2025	08.05.2025

[jaryszek]

Hi Guys,

i have the same issue but i have to exlude from ranges Weekends. 
For example:

06.08.2023

07.08.2023

08.08.2023

09.08.2023

it will be 

07.08.2023

09.08.2023

(06.08.2023 is sunday)

adn for this one:

18.05.2023

19.05.2023

20.05.2023

21.05.2023

22.05.2023

and here it will be 

18.05.2023

22.05.2023

but only with 3 working days (on is weekend day)

Thank you!
Best,
Jacek

[serpiva64 · Accepted solution]

Hi,

you can get this

by apllying this steps 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k1MzsjMS1XSUXJJLAFR/qUlBaUlSrE6cDkFQ6CwgaGegamekYGRKZBjZIApb4Qkb4xF3hhJ3tAUU94MWT8WeXMkeRMs8hZI8mZA+2MB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type date}}),
Date = List.Skip( #"Changed Type"[Date],1),
Custom1 = Date,
Custom2 = List.InsertRange( Custom1,List.Count(Custom1),{null}),
Custom3 = Table.ToColumns(#"Changed Type")&{Custom2},
#"Converted to Table" = Table.FromList(Custom3, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Extracted Values" = Table.TransformColumns(#"Converted to Table", {"Column1", each Text.Combine(List.Transform(_, Text.From), "|"), type text}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Column1", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6"}),
#"Transposed Table" = Table.Transpose(#"Split Column by Delimiter"),
#"Added Index" = Table.AddIndexColumn(#"Transposed Table", "Index", 1, 1, Int64.Type),
#"Inserted Date Subtraction" = Table.AddColumn(#"Added Index", "Subtraction", each if Duration.Days(Date.From([Column4]) - Date.From([Column2]))=1 then null else [Index]),
#"Filled Up" = Table.FillUp(#"Inserted Date Subtraction",{"Subtraction"}),
#"Grouped Rows" = Table.Group(#"Filled Up", {"Subtraction"}, {{"Count", each _, type table [Column1=text, Column2=text, Column3=text, Column4=nullable text, Index=number, Subtraction=number]}, {"MinDate", each List.Min([Column2]), type text}, {"MaxDate", each List.Max([Column2]), type text}}),
#"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Column1", "Column2", "Column3", "Column4", "Index"}, {"Column1", "Column2", "Column3", "Column4", "Index"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Count",{"Subtraction", "Column4", "Index"})
in
#"Removed Columns"

You can see them in the attached file
If this post is useful to help you to solve your issue, consider giving the post a thumbs up and accepting it as a solution!

[CNENFRNL]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k1MzsjMS1UwVNJRMjDUMzDVMzIwMgVyjAyUYnXQ5I2Q5I2xyBsjyRuaYsqbIevHIm+OJG+CRd4CSd4MaH8sAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Machine = _t, Date = _t, Output = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Machine", type text}, {"Date", type date}, {"Output", Int64.Type}}, "fr"),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    #"Grouped Table" = let dates = #"Changed Type"[Date] in Table.Group(#"Added Index", {"Machine", "Index"}, {"Grp", each let dt = [Date] in Table.TransformColumns(_, {"Index", each [Stt = List.Min(dt), End = List.Max(dt)]})}, 0, (x,y) => Byte.From(dates{y[Index]} <> dates{y[Index]-1}+#duration(1,0,0,0))),
    #"Expanded Grp" = Table.ExpandTableColumn(Table.SelectColumns(#"Grouped Table", {"Grp"}), "Grp", {"Date", "Output", "Index"}),
    #"Expanded Index" = Table.ExpandRecordColumn(#"Expanded Grp", "Index", {"Stt", "End"}, {"Stt", "End"})
in
    #"Expanded Index"