How to add 1 to the previous row value if current row is null in POWERQuery

[Singh_10]

Hi All,

I am looking for workaround for the below cae .

Current row is null and we add 1 to the previous row value and put it in current row

I want to fill these null by adding 1 to the previous row value in Power Query

[v-linhuizh-msft · Accepted solution]

Hi @Singh_10 ,

Based on your description, I created this data.

Please follow these steps:

1. Add a custom column.

if [Column1] = null then "NULL" else Text.From([Column1])

2.Grouped rows

3.Expand the data.

4.Choose column1 and fill down.

5. Add a custom column.

if [Custom] = "NULL" then [Column1] + [Index] else Number.FromText([Custom])

The end result is as shown in the image below, and you can remove the columns you don't need.

The overall M code is as follows:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRUitWJVjI2BlMIwtQUTcDMTCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Column1] = null then "NULL" else Text.From([Column1])),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Custom"}, {{"Data", each Table.AddIndexColumn(_, "Index", 1, 1, Int64.Type)}}, GroupKind.Local),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Column1", "Index"}, {"Column1", "Index"}),
    #"Filled Down" = Table.FillDown(#"Expanded Data",{"Column1"}),
    #"Added Custom1" = Table.AddColumn(#"Filled Down", "Custom2", each if [Custom] = "NULL" then [Column1] + [Index] else Number.FromText([Custom]))
in
    #"Added Custom1"

If you have any other questions please feel free to contact me.

Best Regards,
Zhu
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[collinsg]

Good day @Singh_10 ,

An approach is to generate a new list for each column by iterating through each column and generating a value which is either the value in the original list or the previous value plus one. These new lists can then replace the original columns. Here is some code.

1. The first thing to notice is the function fnFillNulls (which is towards the end of the code) - it takes a list (in this case a column) and generates a new list.
2. fnFillNulls is called for the value_min column and the value_max column.
3. A new table is constructed using the original table's headings as its headings but the two new lists as its columns.

let
  Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjYwMjUyMlfSAbEsLAwMlWJ1oiFsYyOwqLGRsYk5VNTYyMTMDCJqamluARbNK83JAQqBKVrwTQzAACgEZBmCWBiqYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [value_min = _t, value_max = _t]),
  #"Changed Type" = Table.TransformColumnTypes(Source,{{"value_min", Int64.Type}, {"value_max", Int64.Type}}),

  fillMins = fnFillNulls(#"Changed Type"[value_min]),
  fillMaxs = fnFillNulls(#"Changed Type"[value_max]),
  tblHeaders = Table.ColumnNames(#"Changed Type"),
  tblColumns = {fillMins} & {fillMaxs},
  combined = Table.FromColumns(tblColumns, tblHeaders),
  #"Changed Type1" = Table.TransformColumnTypes(combined,{{"value_min", Int64.Type}, {"value_max", Int64.Type}}),

  fnFillNulls = (col as list) as list =>
    List.Generate( ()=>
    [i=0, output=col{0}],
    each [i] < List.Count(col),
    each [i=[i]+1, output = if col{i}=null then [output]+1 else col{i}],
    each [output]
  ) 
in
 #"Changed Type1"

The result is (I added rows after your data to show "return to normal" after a run of nulls).

Hope this helps.

[Singh_10]

Hi @collinsg 

Thank you for the reply.

I need this on the basis of ID-- need to group by ID-- one ID have multiple rows on the basis of dates where some dates ahve null values and i need to fill them by adding 1 to previous value min or max on the bases of date or datetime value  and ID