Delete only 2 particular rows

[Nikhill90]

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.

[Greg_Deckler · Accepted solution]

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"

[Eric_Zhang · Accepted solution]

---

@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.

---

@Nikhill90

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"

[Greg_Deckler · Accepted solution]

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"

[dems1bb4]

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