Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Syndicate_Admin
Administrator
Administrator

add rows with missing dates to a table

Hello!

I have the following table:

pescadicto__0-1654269714165.png

and I want to get this table:

pescadicto__1-1654269745390.png

In words: in Power Query I want to intersperse rows with the missing dates/times that differ from each other with a constant value (3 hours in the example, in the real case it is every 3 minutes), for any start date and end date, and for any number of columns.

The values of the new rows must be NULL.

I describe in pseudocode of query steps a type of solution that I imagine:

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,{{"Fecha", type datetime}, {"Columna 1", Int64.Type}, {"Columna 2", Int64.Type}, {"…", Int64.Type}, {"Columna n", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Fecha", Order.Ascending}})

tabla_temp = crear tabla con filas "desde min(columa1) hasta max(columna1) cada 3 horas" y con la misma cantidad de columnas

#"Merge" = merge con tabla_temp

in
    #"Merge"

Maybe the solution is different? I need help getting the second table from the first.

Thank you!

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

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"

View solution in original post

4 REPLIES 4
lbendlin
Super User
Super User

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"

Thank you!! I'll learn from your code.

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!

You 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).

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.