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
let
Source = Your_Source,
Group = Table.Group(Source, {"Complete"}, {{"Data", Table.First}}, GroupKind.Local),
Filter_0 = Table.SelectRows(Group, each [Complete] = 0),
Data = Table.FromRecords(Filter_0[Data])
in
Data
Stéphane