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