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.
Hi,
I have a dataset that has a constantly changing number of rows. I am wanting to remove all rows below a certain cell that will always have the same text value. Is there a way to do this?
Below is an example of the column I am referring to. I am wanting to remove everything below YTD, but as you can see, the column includes the same accounts above and below this.
Forecast |
Account 1 |
Account 2 |
Account 3 |
YTD |
Account 1 |
Account 2 |
Account 3 |
Thank you in advance!
Dean
Solved! Go to Solution.
Hi @deanbland
This Power Query code works - download my example PBIX file
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcssvSk1OLC5RitWJVnJMTs4vzStRMEThGaHwjME8MBEZ4kKKtlgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
YTD_Pos = List.PositionOf(Table.Column(Source,"Column1"),"YTD"),
RemoveRows = Table.RemoveRows(Source,YTD_Pos-1,Table.RowCount(Source)-YTD_Pos+1)
in
RemoveRows
Using this sample data
This sample code requires that the column containing YTD is named Column1 so you'll need to change that to suit your table.
Regards
Phil
Proud to be a Super User!
Hi This thread was extremely useful. I have a question
1) What if I want to delete everything above the YTD Position, how can I tweak this formula?
RemoveRows = Table.RemoveRows(Source,YTD_Pos-1,Table.RowCount(Source)-YTD_Pos+1)
Hi @deanbland
This Power Query code works - download my example PBIX file
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcssvSk1OLC5RitWJVnJMTs4vzStRMEThGaHwjME8MBEZ4kKKtlgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
YTD_Pos = List.PositionOf(Table.Column(Source,"Column1"),"YTD"),
RemoveRows = Table.RemoveRows(Source,YTD_Pos-1,Table.RowCount(Source)-YTD_Pos+1)
in
RemoveRows
Using this sample data
This sample code requires that the column containing YTD is named Column1 so you'll need to change that to suit your table.
Regards
Phil
Proud to be a Super User!
Hi, how would I tweak this to remove everything above YTD?
Thanks
Hi @deanbland
You can use Table.Skip for this
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcssvSk1OLC5RitWJVnJMTs4vzStRMEThGaHwjME8MBEZ4kKKtlgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
YTD_Pos = List.PositionOf(Table.Column(Source,"Column1"),"YTD"),
RemoveRows = Table.Skip(Source,YTD_Pos)
in
RemoveRows
Regards
Phil
Proud to be a Super User!
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.
User | Count |
---|---|
112 | |
97 | |
85 | |
68 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |