It is much faster now @ronrsnfld, but still 2-3 times slower. I'm not saying this because I want to be rude or sometinig... I like to test queries, because this is how I learn.
I've found another issue with your query. Query should delete rows where[vitese] is less than 0,1 only if it is at the beginning or end of the date. You can see my sample data here (or just check your code with my sample data below).
Below is your query with my sample data and you can see that in Result it is missing Source row no 11. (It is less than 0.1 but it is not at the beginning or end of the day)
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rdK7EcMgDADQVXLUPltfhNNlgEzg8/5rRFwcJwXCFCkkPsU7JLFtiS2vlKZkHuxB4gmLp+yh9QpmQF8edWsL8EJAcsNyB0v79CYwJOQg4IqAkOBBoqwhQaNECQnsE/kkLC6k282vkJuCfgbCM9Xzs0doSMhB2BUhIcGjr+CQoD8V4u3UNqEn0f7f+jNTaRMySsSFOLG/AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [index = _t, jour = _t, mois = _t, annee = _t, heure = _t, minute = _t, seconde = _t, vitesse = _t, etat_chaine = _t, date_heure_server = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{
{"index", Int64.Type}, {"jour", Int64.Type}, {"mois", Int64.Type},
{"annee", Int64.Type}, {"heure", Int64.Type}, {"minute", Int64.Type},
{"seconde", Int64.Type}, {"vitesse", type number},
{"etat_chaine", type text}, {"date_heure_server", type datetime}}, "en-US"),
//Group by Date Columns, then generate lists of dates to be removed at
// end and start of dates
#"Grouped Rows" = Table.Group(#"Changed Type", {"jour","mois","annee"}, {
{"Day Start", (t)=> let rc = Table.RowCount(t)-1,
rws = List.Generate(
()=>[r=if t[vitesse]{rc} < 0.1 then t{rc} else null, idx=rc],
each [r] <> null,
each [r = if t[vitesse]{[idx]-1} < 0.1 then t{[idx]-1} else null, idx=[idx]-1],
each [r])
in rws, type {[]}},
{"Day End", (t)=>
List.Generate(
()=>[r=if t[vitesse]{0} < 0.1 then t{0} else null, idx=0],
each [r] <> null,
each [r = if t[vitesse]{[idx]+1} < 0.1 then t{[idx]+1} else null, idx=[idx]+1],
each [r]), type {[]}}}),
#"Rows to Remove" = List.Combine(#"Grouped Rows"[Day Start] & #"Grouped Rows"[Day End]),
#"Remove Rows" = Table.RemoveMatchingRows(#"Changed Type",#"Rows to Remove")
in
#"Remove Rows"
Hi @treza88, I've changed sample data a bit to determine more situations.
Let me know if this is what you want.
Selected lines will be deleted:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rdK7EcMgDADQVXLUPltfhNNlgEzg8/5rRFwcJwXCFCkkPsU7JLFtiS2vlKZkHuxB4gmLp+yh9QpmQF8edWsL8EJAcsNyB0v79CYwJOQg4IqAkOBBoqwhQaNECQnsE/kkLC6k282vkJuCfgbCM9Xzs0doSMhB2BUhIcGjr+CQoD8V4u3UNqEn0f7f+jNTaRMySsSFOLG/AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [index = _t, jour = _t, mois = _t, année = _t, heure = _t, minute = _t, seconde = _t, vitesse = _t, etat_machine = _t, date_heure_server = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source, {{"date_heure_server", type datetime}}, "fr-FR"),
#"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"vitesse", type number}}, "en-US"),
#"Inserted Date" = Table.AddColumn(#"Changed Type1", "Date", each DateTime.Date([date_heure_server]), type date),
#"Added Index" = Table.AddIndexColumn(#"Inserted Date", "Index_1", 1, 1, Int64.Type),
#"Grouped Rows" = Table.Group(#"Added Index", {"Date"}, {{"Min", each List.Min([date_heure_server]), type nullable datetime}, {"Max", each List.Max([date_heure_server]), type nullable datetime}, {"all", each Table.AddIndexColumn(Table.Sort(_, {{"date_heure_server", Order.Ascending}, {"vitesse", Order.Ascending}}), "IndexHelper", 0 , 1, Int64.Type)}, {"Result", each
[ a = Table.AddIndexColumn(Table.Sort(_, {{"date_heure_server", Order.Ascending}, {"vitesse", Order.Ascending}}), "IndexMin", 0 , 1, Int64.Type),
b = Table.AddIndexColumn(Table.Sort(a, {{"date_heure_server", Order.Ascending}, {"vitesse", Order.Descending}}), "IndexMax", 0 , 1, Int64.Type),
min = Table.First(Table.SelectRows(a, (x)=> x[vitesse] >= 0.1))[IndexMin],
max = Table.Last(Table.SelectRows(b, (x)=> x[vitesse] >= 0.1))[IndexMax],
result = Table.SelectRows(b, (x)=> x[IndexMin] >= min and x[IndexMax] <= max)
][result] , type table}}),
#"Combine Result" = Table.Combine(#"Grouped Rows"[Result]),
#"Sorted Rows" = Table.Sort(#"Combine Result",{{"Index_1", Order.Ascending}}),
#"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index_1", "IndexMin", "IndexMax"})
in
#"Removed Columns"
Could you upload Deleted data file somewhere else? I'm trying to download it but it is still processing:
Clicking on the green button doesn't solve the problem.
Raw data link works.
It works. My query above filters out same lines (38137 - 38443) 🙂
Just remember that it filters out [vitesse] less than 0.10. If you want to change it, you can do it here:
- Create blank query
- Open Advanced Editor and replace whole code with one I created
- Close Advanced Editor
- Select Source step (on the right panel)
- Now you should see my code for Source step
- Select whole code and replace it with your table reference i.e. if you have table called Table1 so replace whole code with = Table1 or = #"Table 1" if you have some special characters or space in table name.
- Your table must be like this:
@treza88, if you don't know how to replace that Source step. Try this.
Just replace this address with your csv file address.
let
Source = Csv.Document(File.Contents("Address\raw data.csv"),[Delimiter="|", Columns=1, Encoding=1250, QuoteStyle=QuoteStyle.None]),
#"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6", "Column1.7", "Column1.8", "Column1.9", "Column1.10"}),
#"Promoted Headers" = Table.PromoteHeaders(#"Split Column by Delimiter", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers", {{"date_heure_server", type datetime}}, "fr-FR"),
#"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"vitesse", type number}}, "en-US"),
#"Inserted Date" = Table.AddColumn(#"Changed Type1", "Date", each DateTime.Date([date_heure_server]), type date),
#"Added Index" = Table.AddIndexColumn(#"Inserted Date", "Index_1", 1, 1, Int64.Type),
#"Grouped Rows" = Table.Group(#"Added Index", {"Date"}, {{"Min", each List.Min([date_heure_server]), type nullable datetime}, {"Max", each List.Max([date_heure_server]), type nullable datetime}, {"all", each Table.AddIndexColumn(Table.Sort(_, {{"date_heure_server", Order.Ascending}, {"vitesse", Order.Ascending}}), "IndexHelper", 0 , 1, Int64.Type)}, {"Result", each
[ a = Table.AddIndexColumn(Table.Sort(_, {{"date_heure_server", Order.Ascending}, {"vitesse", Order.Ascending}}), "IndexMin", 0 , 1, Int64.Type),
b = Table.AddIndexColumn(Table.Sort(a, {{"date_heure_server", Order.Ascending}, {"vitesse", Order.Descending}}), "IndexMax", 0 , 1, Int64.Type),
min = Table.First(Table.SelectRows(a, (x)=> x[vitesse] >= 0.1))[IndexMin],
max = Table.Last(Table.SelectRows(b, (x)=> x[vitesse] >= 0.1))[IndexMax],
result = Table.SelectRows(b, (x)=> x[IndexMin] >= min and x[IndexMax] <= max)
][result] , type table}}),
#"Combine Result" = Table.Combine(#"Grouped Rows"[Result]),
#"Sorted Rows" = Table.Sort(#"Combine Result",{{"Index_1", Order.Ascending}}),
#"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index_1", "IndexMin", "IndexMax", "Date"})
in
#"Removed Columns"
Helpful resources
New forum boards available in Real-Time Intelligence.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
Power BI Monthly Update - May 2024
Check out the May 2024 Power BI update to learn about new features.
