Deleting rows by condition

[khinzawhtwe]

Dear Sir/Madam,

In excel power query, I would like to know how to delete rows before specific text of specific column of rate table.

In below table, I want to delete all rows before MMK100. For another table, all rows MMK100 and after MMK100 row are to be deleted

(Because two table is appended with two header rows in one table).

Best Regard,

KZH

[Vijay_A_Verma · Accepted solution]

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

delete all rows before MMK100

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XVLJjQMxDOsl72Ah63YNi1QQpP82dmJS89gXMRBNUtS83w/5SbfH88KSdVCq+N2Pz/PLiA3G1jyosg8uNTI8MBEBU60PtgUZFlQ3P5iJb701amMSWlSHS4aT0Ql1b7i148XOnBzO7JfvYSZwSc0uTNj9nbxev0vkjgzKdsWjwqQKdtF7bBTr+RZGZZCQe91kEDBlYZldw9BGAcb6ZfEMOjmqoW5UL8l/SauQVFmm8FC6J6kYNDzoosSe2puqRv/24CknaRR2CE5WMWlOqebJHwR5nPVnzvmFxzS2HXtcrqSfPw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [USD = _t, GBP = _t, JPY100 = _t, CHF = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    #"Filtered Rows" = Table.SelectRows(#"Added Index", each [Index] >= List.PositionOf(#"Added Index"[JPY100],"MMK100")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index"})
in
    #"Removed Columns"

all rows MMK100 and after MMK100 row are to be deleted

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XVLJjQMxDOsl72Ah63YNi1QQpP82dmJS89gXMRBNUtS83w/5SbfH88KSdVCq+N2Pz/PLiA3G1jyosg8uNTI8MBEBU60PtgUZFlQ3P5iJb701amMSWlSHS4aT0Ql1b7i148XOnBzO7JfvYSZwSc0uTNj9nbxev0vkjgzKdsWjwqQKdtF7bBTr+RZGZZCQe91kEDBlYZldw9BGAcb6ZfEMOjmqoW5UL8l/SauQVFmm8FC6J6kYNDzoosSe2puqRv/24CknaRR2CE5WMWlOqebJHwR5nPVnzvmFxzS2HXtcrqSfPw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [USD = _t, GBP = _t, JPY100 = _t, CHF = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    #"Filtered Rows" = Table.SelectRows(#"Added Index", each [Index] < List.PositionOf(#"Added Index"[JPY100],"MMK100")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index"})
in
    #"Removed Columns"

👍 It's been a pleasure to help you | Help Hours: 11 AM to 9 PM (UTC+05:30)

How to get your questions answered quickly -- How to provide sample data