Forum Discussion
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 ...
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
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