Forum Discussion
Insert Rows for Missing Values of a Column
Need help.
I was given a table that has missing values in the sequence column.
I would like to expand the table with the missing values in the sequence column,
and copy the values from the previous sequence down to fill the new rows.
| ID | Sequence | Sub | Sub1 | Value |
| 0 | 13 | 0 | 0 | 1 |
| 0 | 15 | 0 | 0 | 1 |
| 0 | 18 | 0 | 0 | 1 |
| 0 | 20 | 0 | 0 | 1 |
| 0 | 22 | 0 | 0 | 1 |
| 0 | 12 | 0 | 1 | 1 |
| 0 | 15 | 0 | 1 | 1 |
| 0 | 17 | 0 | 1 | 1 |
| 0 | 18 | 0 | 1 | 1 |
| 0 | 20 | 0 | 1 | 1 |
Realistically, it should look like this. The yellow highlights are the newly added rows.
I'm trying to do this in Power Query but have not had any success so far.
Is there anyway to do this dynamically with Power Query?
Need some expert help with this.
Thanks!
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
5 Replies
you can try this
let
Source = Table,
#"Grouped Rows" = Table.Group(Source, {"Sub1"}, {{"column", each {List.Min([Sequence])..List.Max([Sequence]) }}}),
#"Expanded column" = Table.ExpandListColumn(#"Grouped Rows", "column"),
#"Merged Queries" = Table.NestedJoin(#"Expanded column", {"Sub1", "column"}, Table, {"Sub1", "Sequence"}, "Table", JoinKind.LeftOuter),
#"Expanded Table" = Table.ExpandTableColumn(#"Merged Queries", "Table", {"ID", "Sub", "Value"}, {"ID", "Sub", "Value"}),
#"Filled Down" = Table.FillDown(#"Expanded Table",{"ID", "Sub", "Value"})
in
#"Filled Down"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.