Forum Discussion
Delete only 2 particular rows
Hi All,
I am loading data from Google Analytics, and on one of the days there was an influx of users, which was just a mistake for an hour but since it did happen, the data is now included. Once I load this data, I want to delete just 2 rows at that specific date and time so that my data remains clean. I tried Sorting in descending order and then removing top row, which helps for just 1 row but not the other. There should be a provision to delete just 1 row of data without having to do all this. But right now, if some one could help me, would be really great.
There is, it just isn't available in the GUI, you need to use Table.RemoveRows I think.
https://msdn.microsoft.com/en-us/library/mt260783.aspx
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSs7JL01RitUBsvJzC0pLMvPSwbyUxJJEMCMxLzGnsiQzuRiiCKRcAVNSAYs5WJWhWJKcn1dSlJ+jFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Text = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Text", type text}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1), #"Remove Row 5" = Table.RemoveRows(#"Added Index",4) in #"Remove Row 5"
Nikhill90 wrote:
Hi All,
I am loading data from Google Analytics, and on one of the days there was an influx of users, which was just a mistake for an hour but since it did happen, the data is now included. Once I load this data, I want to delete just 2 rows at that specific date and time so that my data remains clean. I tried Sorting in descending order and then removing top row, which helps for just 1 row but not the other. There should be a provision to delete just 1 row of data without having to do all this. But right now, if some one could help me, would be really great.
You can try to filter out the rows unexpected by specifying the individual datetimes.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIyMDTXNTAEIgVDEysDQysDA6VYnWglIzQ5UysDIytDU7CcMaacMUzOBE3ODGiglaGZUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [id = _t, datetime = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"datetime", type datetime}}), #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([datetime] = #datetime(2017, 1, 1, 14, 1, 0) or [datetime] = #datetime(2017, 1, 1, 16, 0, 16))) in #"Filtered Rows"
3 Replies
There is, it just isn't available in the GUI, you need to use Table.RemoveRows I think.
https://msdn.microsoft.com/en-us/library/mt260783.aspx
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSs7JL01RitUBsvJzC0pLMvPSwbyUxJJEMCMxLzGnsiQzuRiiCKRcAVNSAYs5WJWhWJKcn1dSlJ+jFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Text = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Text", type text}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1), #"Remove Row 5" = Table.RemoveRows(#"Added Index",4) in #"Remove Row 5"fantastic!
you just need to be REALLY careful what you type.
took me numerous attempts to get it right (as I'm pretty new to this though).
let Source = Excel.Workbook( File.Contents("D:\_Ulm\# ECE Data Analyst\Power BI\LTE_eNB_Feature_Build_Plan 960051.xlsm"), null, true), cap_Sheet = Source{[Item="cap", Kind="Sheet"]}[Data], #"Changed Type" = Table.TransformColumnTypes(cap_Sheet,{{"Column1", type any}, {"Column2", type any}, {"Column3", type any}, …, {"Column941", type any}}), #"Removed Top Rows" = Table.Skip(#"Changed Type",3), #"Removed Bottom Rows" = Table.RemoveLastN(#"Removed Top Rows",22), #"Removed Bottom Rows1" = Table.RemoveLastN(#"Removed Bottom Rows",1), #"Remove Row 2 only" = Table.RemoveRows(#"Removed Bottom Rows1",1,1), #"Remove Row 3and4" = Table.RemoveRows(#"Remove Row 2 only",1,2) in #"Remove Row 3and4"with
#"Remove Row 2 only" = Table.RemoveRows(#"Removed Bottom Rows1",1,1),
...it removes just the 2nd row, and with
#"Remove Row 3and4" = Table.RemoveRows(#"Remove Row 2 only",1,2)
...it removes row 2 and 3 of what's left over after the previous step.
notes:
in command Table.RemoveRows
- the 1st parameter is the reference to the previous step name
- the 2nd parameter is the absolute row number of what's left after all previous steps
=> as row 2 was to be deleted and parameter is 1 it's obvious that the row counting starts with 0 - the 3rd parameter is the number of rows to be removed
Nikhill90 wrote:
Hi All,
I am loading data from Google Analytics, and on one of the days there was an influx of users, which was just a mistake for an hour but since it did happen, the data is now included. Once I load this data, I want to delete just 2 rows at that specific date and time so that my data remains clean. I tried Sorting in descending order and then removing top row, which helps for just 1 row but not the other. There should be a provision to delete just 1 row of data without having to do all this. But right now, if some one could help me, would be really great.
You can try to filter out the rows unexpected by specifying the individual datetimes.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIyMDTXNTAEIgVDEysDQysDA6VYnWglIzQ5UysDIytDU7CcMaacMUzOBE3ODGiglaGZUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [id = _t, datetime = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"datetime", type datetime}}), #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([datetime] = #datetime(2017, 1, 1, 14, 1, 0) or [datetime] = #datetime(2017, 1, 1, 16, 0, 16))) in #"Filtered Rows"
