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

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

6 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

    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-Forum/ba-p/963216
    Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

  • 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
  • dufoq3's avatar
    dufoq3
    Community Champion
    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
  • slorin's avatar
    slorin
    Super User
    1 year ago

    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