Forum Discussion

rishghuwalewala's avatar
rishghuwalewala
New Member
3 years ago
Solved

Fill Down with a Condition

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 haveWhat I want
11
null2
null3
null4
null5
null6
11
null2
null3
null4
11
null2
null3

 

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

Need Help
  • kirete17's avatar
    kirete17
    3 years ago

    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

2 Replies

  • kirete17's avatar
    kirete17
    Frequent Visitor
    3 years ago

    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's avatar
    cpwebb
    Microsoft Employee
    3 years ago

    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