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 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.
Solved! Go to 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"
@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"
@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"
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
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 |
---|---|
114 | |
100 | |
78 | |
75 | |
50 |
User | Count |
---|---|
144 | |
109 | |
108 | |
88 | |
61 |