Forum Discussion

Regular Visitor

1 year ago

Solved

Fill down with index value

I am trying to fill down in null value, i have tried group by and it does not work. As i have simlar value in the same column. Last column is what i want. I currently have first 2 columns. Any idea how to achieve this?

Custom	Index.1			What I want
1	0			1
2	1			2
3	2			3
4	3			4
5	4			5
6	5			6
7	6			7
8	7			8
7	8			7
8	9			8
6	10			6
7	11			7
null	12			8
null	13			9
null	14			10
4	15			4
5	16			5
null	17			6
null	18			7
null	19			8

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "Vc5LDsAgCATQu7B2IShKz2K8gemu9++UJiTs3vAJrEVMhSrtskggdjVIXB1qLoW6a0DqmtBwGTSjZlG7YoNrtPm/cj/nfElSain1+II13uCRZmZKlhLu7xc=", 
          BinaryEncoding.Base64
        ), 
        Compression.Deflate
      )
    ), 
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [Custom = _t, Index.1 = _t]
  ), 
  #"Changed Type" = Table.TransformColumnTypes(
    Source, 
    {{"Index.1", Int64.Type}, {"Custom", Int64.Type}}
  ), 
  #"Replaced Value" = Table.ReplaceValue(
    #"Changed Type", 
    each [Custom], 
    (k) =>
      if k[Custom] = null then
        let
          t = Table.Last(
            Table.SelectRows(#"Changed Type", each [Index.1] < k[Index.1] and [Custom] <> null)
          )
        in
          k[Index.1] - t[Index.1] + t[Custom]
      else
        k[Custom], 
    Replacer.ReplaceValue, 
    {"Custom"}
  )
in
  #"Replaced Value"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.

1 Reply

Super User

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "Vc5LDsAgCATQu7B2IShKz2K8gemu9++UJiTs3vAJrEVMhSrtskggdjVIXB1qLoW6a0DqmtBwGTSjZlG7YoNrtPm/cj/nfElSain1+II13uCRZmZKlhLu7xc=", 
          BinaryEncoding.Base64
        ), 
        Compression.Deflate
      )
    ), 
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [Custom = _t, Index.1 = _t]
  ), 
  #"Changed Type" = Table.TransformColumnTypes(
    Source, 
    {{"Index.1", Int64.Type}, {"Custom", Int64.Type}}
  ), 
  #"Replaced Value" = Table.ReplaceValue(
    #"Changed Type", 
    each [Custom], 
    (k) =>
      if k[Custom] = null then
        let
          t = Table.Last(
            Table.SelectRows(#"Changed Type", each [Index.1] < k[Index.1] and [Custom] <> null)
          )
        in
          k[Index.1] - t[Index.1] + t[Custom]
      else
        k[Custom], 
    Replacer.ReplaceValue, 
    {"Custom"}
  )
in
  #"Replaced Value"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.