Forum Discussion
Solved
keep next record with change
I need help to extract specific records in the Power Query Editor. In the example here, I created a custom column for sorting and would like to keep the record when the suffix changes from 1 to 0. ...
Hi tracyhopaulson, index is just to show you row number. Index is not necessary for this purpose:
Before
After
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjW3NDQ1N7XUNdQ1VIrVGRoCBsQIxAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Custom.1 = _t]), AddedIndex = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type), FilteredRows = Table.FirstN(Table.SelectRows(AddedIndex, each let a = Text.ToList(Text.End([#"Custom.1"], 3)) in a{0} = "1" and a{2} = "0"), 1) in FilteredRows
Hi tracyhopaulson, index is just to show you row number. Index is not necessary for this purpose:
Before
After
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjW3NDQ1N7XUNdQ1VIrVGRoCBsQIxAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Custom.1 = _t]),
AddedIndex = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
FilteredRows = Table.FirstN(Table.SelectRows(AddedIndex, each let a = Text.ToList(Text.End([#"Custom.1"], 3)) in a{0} = "1" and a{2} = "0"), 1)
in
FilteredRows
Thank you, this works 🙂
You're welcome 😉