Fill Down with a Condition

[rishghuwalewala]

Hi Community,

Would anyone know how to fill down using incremental values from previous row. Example of the output I'm looking for is below:

What I have	What I want
1	1
null	2
null	3
null	4
null	5
null	6
1	1
null	2
null	3
null	4
1	1
null	2
null	3

Please note I'm only looking for a solution in Power Query. Would anyone know how to solve this?

[kirete17 · Accepted solution]

Table.Group can be the solution

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Custom1 = 
        Table.Combine(
            Table.Group( 
                Source, 
                "What I have", 
                { "q", each Table.AddIndexColumn( _, "What I want", 1, 1 ) }, 
                0, 
                (x,y)=> Byte.From( y <> "null" ) 
            )[q]
        )
in
    Custom1

[cpwebb]

Here's the M code showing how you can solve this with List.Generate:

let
    //Create the source table
    Source = {1,null,null,null,null,null,1,null,null,null,1,null,null},
    ToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    //Do the calculation
    SourceColumn = ToTable[Column1],
    OutputColumn = List.Generate(
        ()=>[Position=0, RT=if SourceColumn{0}=null then null else 1],
        each [Position]<List.Count(SourceColumn),
        each [Position=[Position]+1, RT=if SourceColumn{[Position]+1}=1 then 1 else [RT]+1],
        each [RT]
    ),
    //Prepare the output table with the two columns
    ZipLists = List.Zip({SourceColumn, OutputColumn}),
    OutputTable = #table({"Input", "Output"}, ZipLists)
in
    OutputTable

This problem is a variant on a running total and I recommend you read Rick de Groot's excellent article on this topic here https://gorilla.bi/power-query/running-total/ to get more background.

Two things to think about:

1) What do you want to do if the first value isn't 1, if that's possible?

2) You may want to buffer the table in memory to get better performance, as discussed in Rick's article

Chris