Forum Discussion
yhong16
1 year agoRegular Visitor
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 h...
- 1 year ago
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.
lbendlin
1 year agoSuper 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.