Forum Discussion
Insert Rows for Missing Values of a Column
ryan_mayu
This works!
But since I have millions of row in the table, is there a way to do it in a single query without the merging of tables?
I believe the merge will slow load time.
Maybe a group within a group, then expand out to reveal the full sequence.
Something like this, but haven't got it to work yet.:I think we have to merge tables However, this time we don't create a new table
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlDSUTI0BhIGUGyoFKsDFTbFLmyBVdjIALuwEXZDjOBCWK1EEzbHLmyBVdjIAFU4FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Sequence = _t, Sub = _t, Sub1 = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Sequence", Int64.Type}}),
Custom1 = Table.Group(#"Changed Type" , {"Sub1"}, {{"column", each {List.Min([Sequence])..List.Max([Sequence]) }}}),
Table = Table.ExpandListColumn(Custom1, "column"),
Custom2 = Table.NestedJoin(Table, {"Sub1", "column"}, #"Changed Type", {"Sub1", "Sequence"}, "Table", JoinKind.LeftOuter),
#"Expanded Table" = Table.ExpandTableColumn(Custom2, "Table", {"ID", "Sub", "Value"}, {"ID", "Sub", "Value"}),
#"Filled Down" = Table.FillDown(#"Expanded Table",{"ID", "Sub", "Value"}),
#"Sorted Rows" = Table.Sort(#"Filled Down",{{"Sub1", Order.Ascending}, {"column", Order.Ascending}})
in
#"Sorted Rows"pls see the attachment below
ryan_mayu
This works!
But since I have millions of row in the table, is there a way to do it in a single query without the merging of tables?
I believe the merge will slow load time.
Maybe a group within a group, then expand out to reveal the full sequence.
Something like this, but haven't got it to work yet.:
I think we have to merge tables However, this time we don't create a new table
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlDSUTI0BhIGUGyoFKsDFTbFLmyBVdjIALuwEXZDjOBCWK1EEzbHLmyBVdjIAFU4FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Sequence = _t, Sub = _t, Sub1 = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Sequence", Int64.Type}}),
Custom1 = Table.Group(#"Changed Type" , {"Sub1"}, {{"column", each {List.Min([Sequence])..List.Max([Sequence]) }}}),
Table = Table.ExpandListColumn(Custom1, "column"),
Custom2 = Table.NestedJoin(Table, {"Sub1", "column"}, #"Changed Type", {"Sub1", "Sequence"}, "Table", JoinKind.LeftOuter),
#"Expanded Table" = Table.ExpandTableColumn(Custom2, "Table", {"ID", "Sub", "Value"}, {"ID", "Sub", "Value"}),
#"Filled Down" = Table.FillDown(#"Expanded Table",{"ID", "Sub", "Value"}),
#"Sorted Rows" = Table.Sort(#"Filled Down",{{"Sub1", Order.Ascending}, {"column", Order.Ascending}})
in
#"Sorted Rows"pls see the attachment below
ryan_mayu
Both solutions worked out! Thank You.
The second solution is better suited so as not to create a second table external to the initial query.
Though, for better efficiency, the internal table will need to be stored in a table buffer (Table.Buffer) since it's being used as a reference table.
I'm still trying to see if there's a way to do this without the internal merge. Maybe there's a way?
If not, your solution is solid. Thank You for your time and help.you are welcome.