Forum Discussion

khinzawhtwe's avatar
khinzawhtwe
Regular Visitor
3 years ago
Solved

Deleting rows by condition

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

Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    3 years ago

    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

1 Reply

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    3 years ago

    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