Forum Discussion
Syndicate_Admin
Administrator
4 years agoadd rows with missing dates to a table
Hello! I have the following table: and I want to get this table: In words: in Power Query I want to intersperse rows with the missing dates/times that differ from each other with ...
- 4 years ago
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc89CoAwDIbhq5TOQtP82bq5CoJ76RXcvL9SEIo1EHinh4+U4oHCdp0BAdEBLQDPuXX3kxfSlM3W6WO1t5EF1exg392j2cSEZs3dZkV4jmYHm3ubWRnNNss/tv2bKCmbrfUG", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Fecha = _t, #"Columna 1" = _t, #"Columna 2" = _t, #"…" = _t, #"Columna n" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Columna 1", Int64.Type}, {"Columna 2", Int64.Type}, {"…", Int64.Type}, {"Columna n", Int64.Type}}), #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Fecha", type datetime}}, "es-MX"), NewTable = List.Generate(()=>List.First(#"Changed Type with Locale"[Fecha]),each _ <= List.Last(#"Changed Type with Locale"[Fecha]),each _ + #duration(0,3,0,0)), #"Converted to Table" = Table.FromList(NewTable, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Merged Queries" = Table.NestedJoin(#"Changed Type with Locale", {"Fecha"}, #"Converted to Table", {"Column1"}, "Merged", JoinKind.RightOuter), #"Expanded Merged" = Table.ExpandTableColumn(#"Merged Queries", "Merged", {"Column1"}, {"Column1"}), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Merged",{{"Column1", type datetime}}), #"Replaced Value" = Table.ReplaceValue(#"Changed Type1",null,each [Column1],Replacer.ReplaceValue,{"Fecha"}), #"Removed Columns" = Table.RemoveColumns(#"Replaced Value",{"Column1"}), #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"Fecha", Order.Ascending}}) in #"Sorted Rows"
Syndicate_Admin
Administrator
4 years agoThank you!! I'll learn from your code.
lbendlin
Super User
4 years agoYou were already on the right track. Generate a list of all possible timestamps and then do an outer join. Learn about the difference between Table.SelectColumns and Table.RemoveColumns (normally the first one is preferred but in your scenario the second one needs to be used).