add new lines in power query

[richard_first]

i will have a table which i have already loaded from power query, but the number in the batch column is discontiguous. what I want power query do is to add new lines and fill in the missing number in the column batch to make the sequence contiguously and fill in the batch_result with "successfully".

batch	batch_result
515​	CANCELLED
506​	CANCELLED
501​	CANCELLED
497​	CANCELLED
483​	CANCELLED
476​	CANCELLED
466​	CANCELLED
461​	CANCELLED
444​	CANCELLED

thank you in advance.

 

[alansidman]

How about showing us your source table and your Mcode so we can see what you have done and what needs to happen to get your expected results. Additionally for the inserted batches, what do you expect the batch result to state. We are excel helpers here, not mind readers.

 

[richard_first]

How about showing us your source table and your Mcode so we can see what you have done and what needs to happen to get your expected results. Additionally for the inserted batches, what do you expect the batch result to state. We are excel helpers here, not mind readers.

hi alansidman,
thank you for your reply.
i am sorry i cannot attach the source table, because i load it from a table in the intranet of my company.
the table i mentioned above is the result i have loaded from the internal table.
in the source table the numbers in the batch column are discontiguous.
what i want power query do is to change the table as following:

batch	batch_result
515​	CANCELLED
514​	successful
513​	successful
512​	successful
511​	successful
510​	successful
509​	successful
508​	successful
507​	successful
506​	CANCELLED
505​	successful
504​	successful
503​	successful
502​	successful
501​	CANCELLED
500​	successful
499​	successful
498​	successful
497​	CANCELLED
496​	successful
495​	successful
494​	successful
493​	successful
492​	successful
491​	successful
490​	successful
489​	successful
488​	successful
487​	successful
486​	successful
485​	successful
484​	successful
483​	CANCELLED

 

[sandy666]

try

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    List = {444..515},
    C2T = Table.FromList(List, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Ren = Table.RenameColumns(C2T,{{"Column1", "batch"}}),
    SortD = Table.Sort(Ren,{{"batch", Order.Descending}}),
    Join = Table.NestedJoin(SortD,{"batch"},Source,{"batch"},"Table1",JoinKind.LeftOuter),
    Expand = Table.ExpandTableColumn(Join, "Table1", {"batch_result"}, {"batch_result"}),
    Replace = Table.ReplaceValue(Expand,null,"successfully",Replacer.ReplaceValue,{"batch_result"})
in
    Replace

batch	batch_result		batch	batch_result
515	CANCELLED		515	CANCELLED
506	CANCELLED		514	successfully
501	CANCELLED		513	successfully
497	CANCELLED		512	successfully
483	CANCELLED		511	successfully
476	CANCELLED		510	successfully
466	CANCELLED		509	successfully
461	CANCELLED		508	successfully
444	CANCELLED		507	successfully
			506	CANCELLED
			505	successfully
			504	successfully
			503	successfully
			502	successfully
			501	CANCELLED
			500	successfully
			499	successfully
			498	successfully
			497	CANCELLED
			496	successfully
			495	successfully
			494	successfully
			493	successfully
			492	successfully
			491	successfully
			490	successfully
			489	successfully
			488	successfully
			487	successfully
			486	successfully
			485	successfully
			484	successfully
			483	CANCELLED
			482	successfully
			481	successfully
			480	successfully
			479	successfully
			478	successfully
			477	successfully
			476	CANCELLED
			475	successfully
			474	successfully
			473	successfully
			472	successfully
			471	successfully
			470	successfully
			469	successfully
			468	successfully
			467	successfully
			466	CANCELLED
			465	successfully
			464	successfully
			463	successfully
			462	successfully
			461	CANCELLED
			460	successfully
			459	successfully
			458	successfully
			457	successfully
			456	successfully
			455	successfully
			454	successfully
			453	successfully
			452	successfully
			451	successfully
			450	successfully
			449	successfully
			448	successfully
			447	successfully
			446	successfully
			445	successfully
			444	CANCELLED

 

[sandy666]

or more flexible

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    cnt = Table.RowCount(Source)-1,
    First = Source[batch]{0},
    Last = Source[batch]{cnt},
    List = {Last..First},
    C2T = Table.FromList(List, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Ren = Table.RenameColumns(C2T,{{"Column1", "batch"}}),
    SortD = Table.Sort(Ren,{{"batch", Order.Descending}}),
    Join = Table.NestedJoin(SortD,{"batch"},Source,{"batch"},"Table1",JoinKind.LeftOuter),
    Expand = Table.ExpandTableColumn(Join, "Table1", {"batch_result"}, {"batch_result"}),
    Replace = Table.ReplaceValue(Expand,null,"successfully",Replacer.ReplaceValue,{"batch_result"})
in
    Replace

 

[shaowu459]

let
    Source = Excel.CurrentWorkbook(){[Name="Table26"]}[Content],
    toRows=Table.ToRows(Source),
    res=List.Accumulate(
                            List.Skip(toRows),
                            {toRows{0}},
                            (x,y)=>
                                    x&List.Transform({1..List.Last(x){0}-y{0}-1},each {List.Last(x){0}-_,"Successful"})&{y}
                       )
in
    #table({"Batch","Result"},res)