Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.