Forum Discussion
Syndicate_Admin
4 years agoAdministrator
add 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
4 years agoAdministrator
Thank you!! I'll learn from your code.
Syndicate_Admin
4 years agoAdministrator
I just realized that I am commenting with the pescadicto user to a question from the user pescadicto_
I always used pescadicto, in my last login I had to use my organizational account and a new user was created (which I called pescadicto_ and with whom I asked the question above), in today's login the old user, pescadicto, automatically entered.
Anyway... I don't understand what's going on.
Thank you from both users!