Forum Discussion
Make continuous ranges from date list
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 |
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!
3 Replies
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!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"
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
