Forum Discussion

tracyhopaulson's avatar
tracyhopaulson
Resolver I
1 year ago
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.  ...
Data Protection
Need Help
  • dufoq3's avatar
    dufoq3
    1 year ago

    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
ZhangKun's avatar
ZhangKun
Super User
1 year ago

you'd provide sample data.the following code is a query with a similar function

let
    源 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ1VNJRMlSK1YGxjcBsYzDbGMw20TUAsk2g4iC2KZJ6MygbJG6OxLZAYlsiqTc0QOYAbY4FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t]),
    更改的类型 = Table.TransformColumnTypes(源,{{"A", type text}, {"B", type text}}),
    已添加自定义 = Table.AddColumn(更改的类型, "C", each Text.End([A], 1)),
    分组的行 = Table.Group(已添加自定义, {"C"}, {{"计数", each _}}, GroupKind.Local, (x, y) => Number.From(x[C] <> y[C])),
    筛选的行 = Table.SelectRows(分组的行, each ([C] = "0")),
    已添加自定义1 = Table.AddColumn(筛选的行, "自定义", each [计数]{0})
in
    已添加自定义1