keep next record with change

[tracyhopaulson]

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.  In this case, it is row 11 only. Can someone help please? Thanks

[slorin]

Hi @tracyhopaulson 

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 

[dufoq3 · Accepted solution]

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

[tracyhopaulson]

Thank you, this works 🙂

[dufoq3]

You're welcome 😉

[ZhangKun]

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

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...