PowerQuery M code duplicate row to fill missing sequence

[RandyShroyer]

New to PowerQuery and M.......

I have a table that is roughly like this....

INDEX        DATA                             SEQ #

123            [multiple columns]         1
123            [multiple columns]         2

123            [multiple columns]         5

456            [multiple columns]         2

456            [multiple columns]         3

456            [multiple columns]         5

456            [multiple columns]         7

What I want to do is fill in the gaps in the SEQ # sequence for each KEY by duplicating the preceding row for the KEY and changing a few of the data columns.

So the result would be this with the bolded rows being the new rows added:

123            [multiple columns]         1
123            [multiple columns]         2

123            [modified columns]         3

123            [Modified columns]         4

123            [multiple columns]         5

456            [multiple columns]         2

456            [multiple columns]         3

456            [modified columns]         4

456            [multiple columns]         5

456            [modified columns]         6

456            [multiple columns]         7

I will know the starting and ending seq number for each KEY.

Any ideas?

[v-angzheng-msft]

Hi, @RandyShroyer 

May I ask if your problem has been solved? Is the above post helpful to you?

If  it does, could you please mark the post which help as Answered? 

Or you can take a look at my solution with DAX in the thread below.
How to find missing values (QAQC)

Hope this helps.


Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[RandyShroyer]

Looks good!  Have more testing to do, but so far looks like this will do the trick!

[RandyShroyer]

I'm still working on it.  Have been pulled into other things as well.  I certainly will respond appropriately when I am done implementing it.  Thanks for helping!

[Vijay_A_Verma · Accepted solution]

Copy this in a blank query to see the steps

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyPrRASUcpDIjDgdgbiA8tMFSK1UHIgcRdoPjQAiMUuVAg9gdiH7CcKVjOxNQMLOcHxAFQs2H6YHKOQBwFxEFgOWMUOTeouC8WM52B2B2q5tACc6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [INDEX = _t, Data1 = _t, Data2 = _t, Data3 = _t, #"SEQ#" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"INDEX", Int64.Type}, {"Data1", type text}, {"Data2", type text}, {"Data3", type text}, {"SEQ#", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"INDEX"}, {{"MinSeq", each List.Min([#"SEQ#"]), type text}, {"MaxSeq", each List.Max([#"SEQ#"]), type text}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each {[MinSeq]..[MaxSeq]}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"MinSeq", "MaxSeq"}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"INDEX", "SEQ#"}, #"Removed Columns", {"INDEX", "Custom"}, "Removed Columns", JoinKind.FullOuter),
    #"Expanded Removed Columns" = Table.ExpandTableColumn(#"Merged Queries", "Removed Columns", {"INDEX", "Custom"}, {"Removed Columns.INDEX", "Removed Columns.Custom"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Removed Columns",{{"Removed Columns.INDEX", Order.Ascending}, {"Removed Columns.Custom", Order.Ascending}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows",{"INDEX", "SEQ#"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"Removed Columns.INDEX", "Data1", "Data2", "Data3", "Removed Columns.Custom"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Removed Columns.INDEX", "INDEX"}, {"Removed Columns.Custom", "SEQ#"}}),
    #"Filled Down" = Table.FillDown(#"Renamed Columns",{"Data1", "Data2", "Data3"})
in
    #"Filled Down"

[wdx223_Daniel]

assume INDEX is the first column, adn SEQ is the third column. then try this code

NewStep=#table(Table.ColumnNames(PreviousStepName),List.Accumulate(Table.ToRows(PreivousStepName),{{},"",0},(x,y)=>if y{0}<>x{2} then {x{0}&{y},y,y{0}} else {x{0}&List.Transform({x{1}{2}+1..y{2}},each List.ReplaceRange(x{1},2,1,{_})),y,y{0}}){0})

[RandyShroyer]

Thank you!  I went with the other posted solution

[RandyShroyer]

KEY and INDEX are the same thing - sorry for the confusion.