Forum Discussion
Select Rows based on Timestamp in one table and Start End Fields on a Second Table
- 2 years ago
I spent around 2 hours with this query. I tried different versions of code because I wanted to speed it up, but txt source is too slow and you need to do a lot of comparism. I'm not able to make it faster. Maybe someone else can help.
Change address to your folder in Source step.
let Source = Folder.Files("c:\Downloads\PowerQueryForum\VHalpenny\"), FilteredSessionsTable = Table.SelectRows(Source, each ([Name] = "SessionsTable.xlsx")), Content = FilteredSessionsTable{0}[Content], ImportedExcelWorkbook = Excel.Workbook(Content, true), Sheet1 = ImportedExcelWorkbook{[Item="Sheet1",Kind="Sheet"]}[Data], FilteredRows1 = Table.SelectRows(Sheet1, each ([T200] <> null)), RemoveMiliseconds = Table.TransformColumns(FilteredRows1, List.Transform({"T200", "T300"}, (x)=> { x, each DateTime.From(DateTime.ToText(_, [Format="yyyy-MM-dd hh:mm:ss"])) })), Ad_DateTimeList = Table.AddColumn(RemoveMiliseconds, "DateTimeList", each List.DateTimes([T200], Duration.TotalSeconds([T300] - [T200])+1, #duration(0,0,0,1)), type list), Ad_MergeKey1 = Table.AddColumn(Ad_DateTimeList, "MergeKey", each if Time.Minute([T200]) = Time.Minute([T300]) then { Number.From(DateTime.ToText([T200], "yyyyMMddhhmm")) } else { Number.From(DateTime.ToText([T200], "yyyyMMddhhmm")), Number.From(DateTime.ToText([T300], "yyyyMMddhhmm")) }, type list), // Niektore riadky su zduplikovane kvoli MERGE neskor. Jedna sa o riadky kedy minuta z [T200] nie je rovna minute z [T300] ExpandedMergeKey1 = Table.ExpandListColumn(Ad_MergeKey1, "MergeKey"), TableSessions = Table.Buffer(Table.SelectColumns(ExpandedMergeKey1,{"Operator", "MergeKey", "DateTimeList"})), StepBack = Source, FiteredOperatorTextFiles = Table.SelectRows(StepBack, each Text.StartsWith([Name], "Operator", Comparer.OrdinalIgnoreCase) and [Extension] = ".txt" ), RemovedOtherColumns1 = Table.SelectColumns(FiteredOperatorTextFiles,{"Content", "Name"}), Ad_Data = Table.AddColumn(RemovedOtherColumns1, "Data", each [ a = Table.TransformColumnNames(Table.PromoteHeaders(Csv.Document([Content],[Delimiter=";", Columns=3, Encoding=1250, QuoteStyle=QuoteStyle.None])), Text.Trim), //csv to table + promote headers + trim column names b = Table.AddColumn(a, "Operator", (x)=> Text.BeforeDelimiter([Name], "_"), type text), //Add Operator column c = Table.AddColumn(b, "System", (x)=> Text.Proper(Text.BetweenDelimiters(Text.Lower([Name]), "_", ".txt")), type text) //Add System column //d = Table.FirstN(c, RepeatValue) //for query creating purpose (to load less data) ][c], type table), CombinedData = Table.Combine(Ad_Data[Data]), ChangedType = Table.TransformColumnTypes(CombinedData,{{"Time", type datetime}, {"MR-DC DRB PDCP DL Throughput Total (kbps)", type number}, {"MR-DC DRB PDCP UL Throughput Total (kbps)", type number}}, "en-US"), FilteredRows2 = Table.SelectRows(ChangedType, each [Time] <> null), RemoveMiliseconds2 = Table.TransformColumns(FilteredRows2, {{"Time", each DateTime.From(DateTime.ToText(_, [Format="yyyy-MM-dd hh:mm:ss"]))}}), Ad_MergeKey2 = Table.AddColumn(RemoveMiliseconds2, "MergeKey", each Number.From(DateTime.ToText([Time], "yyyyMMddhhmm")), Int64.Type), GroupedRows = Table.Group(Ad_MergeKey2, {"Operator", "MergeKey"}, {{"All", each _, type table [Time=nullable datetime, #"MR-DC DRB PDCP DL Throughput Total (kbps)"=nullable number, #"MR-DC DRB PDCP UL Throughput Total (kbps)"=nullable number, Operator=text, System=text, Date=date, Hour=number]}}), MergedQueries = Table.NestedJoin(GroupedRows, {"Operator", "MergeKey"}, TableSessions, {"Operator", "MergeKey"}, "TableSessions", JoinKind.LeftOuter), FilteredRows3 = Table.SelectRows(MergedQueries, each Table.RowCount([TableSessions]) <> 0), Ad_TableSessionDateTimes = Table.AddColumn(FilteredRows3, "TableSession_DateTimes", each List.Combine([TableSessions][DateTimeList]), type list), RemovedOtherColumns2 = Table.SelectColumns(Ad_TableSessionDateTimes,{"All", "TableSession_DateTimes"}), ExpandedAll = Table.ExpandTableColumn(RemovedOtherColumns2, "All", {"Time", "MR-DC DRB PDCP DL Throughput Total (kbps)", "MR-DC DRB PDCP UL Throughput Total (kbps)", "Operator", "System"}, {"Time", "MR-DC DRB PDCP DL Throughput Total (kbps)", "MR-DC DRB PDCP UL Throughput Total (kbps)", "Operator", "System"}), Ad_KeepRow = Table.AddColumn(ExpandedAll, "Keep Row", each List.Contains(List.Buffer([TableSession_DateTimes]), [Time]), type logical), FilteredRows4 = Table.SelectRows(Ad_KeepRow, each [Keep Row] = true) in FilteredRows4
I'm sorry, but don't be that lazy and check your data before posting please!
There were no match between Session and Event table, so I've edited your sample.
Result
let
SessionTable = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZVLihwxDECvEno9Y/S1LK9nnwMMvQihF71K6Aw5f2T1rlrBBQVleMUroZ8/Py/ff98eP75+PfDydjECMOmkcf543P/e4v3z/nW//YkDAfE72DvBN7ApfTI1NS+JJ7m+FXqNr/d6pQmjoVBFUJPUehM4oV9hNlepiHKSSj+AcZucMYEnUWPTkniSWu+CJ/Q+ZbTux+iTqCYp9fHfE9FzeKK0fiztk3iSUi/MstcLT4Fm3ktiSWq947ZzQuITvZFjRagnKfUq6nu96gRtNqwiSEkqvQPI2Ol9AkyVZnTMfRCMJ0mt73hG3ydpMVZJYp7/N1aOqNvSRuJpxYj9mPsnGUlqvbGd0MfUSlM59n0SxSSlnhi2pY3m4Im9gUFFiJPU+uj8E/poEWgDjn3/JJak1LPQidyzTIZYXK+ds4glKfUCeCJ6gTVWMYMVibFapNYb9RN6m8yt22tpg8S6WKTUK8N2Y8ZS5NV+ysepTRLXySK1fuh254RkDW7E+No5i1iSQo+xE7a3FcIEWfueuVck9v0ipR5hu5BDEo9g6wIlGUlqfd9ehkvS12XYRUviSa7Xfw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Operator = _t, Sequence = _t, #"Point of Interest" = _t, #"Infrastructure Type Fine" = _t, T200 = _t, T300 = _t]),
SessionTable_ChangedType = Table.TransformColumnTypes(SessionTable,{{"Sequence", Int64.Type}, {"T200", type datetime}, {"T300", type datetime}}),
MappingTable = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8i9ILUosyS8yVNJRCq4sLknNjXfLzEk1VIrVwSlphE/SGEXSCFXSRCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Operator = _t, File = _t]),
EventTable = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZNBS8QwEIX/SuhJQcdMJs2kzc0tnhSWtZ7KsigUd3GXXdzuwX9v2uhFkE5o6Ol7L0Pem64rHnb7PrS7Qx+eVrfNQjWre7VsFkvVPKp2+3m8vG9Pl0G1x+F1r64+3k7n67/ky79ksb7piuev89AfNuNFGPjO6HgMKe1r4rq0UGodgoj0lReR5eQZPwHrJhaBTXBgSKDgDHefwVZS1tdap6k9GA7I4FGgwQx/M7FkADkQQckCDWX42x8WHbAT8CnRkWaRv8uYJeVJgD7+jEAgDjWy1e/gJk0/L0Ett0cEquz8Tkyk3NVksCl1tD7mrylU3sHsEkVZKoDFisaKIVuBRrzWkc0oAKYCOAaiQPGdYiHX3w==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
EventTable_Split = Table.SplitColumn(EventTable, "Column1", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv)),
EvetnTable_PromotedHeaders = Table.PromoteHeaders(EventTable_Split, [PromoteAllScalars=true]),
EventTable_ChangedType = Table.TransformColumnTypes(EvetnTable_PromotedHeaders,{{"Time", type datetime}, {"MR-DC DRB PDCP DL Throughput Total (kbps)", type number}, {"MR-DC DRB PDCP UL Throughput Total (kbps)", type number}}, "en-US"),
// Merged Session and Mapping
MergedQueriesMapping = Table.NestedJoin(SessionTable_ChangedType, {"Operator"}, MappingTable, {"Operator"}, "MappingTable", JoinKind.LeftOuter),
ExpandedMappingTable = Table.ExpandTableColumn(MergedQueriesMapping, "MappingTable", {"File"}, {"File"}),
MergedQueriesEvent = Table.NestedJoin(ExpandedMappingTable, {"File"}, EventTable_ChangedType, {"File"}, "EventTable_ChangedType", JoinKind.LeftOuter),
FilteredRows = Table.SelectRows(MergedQueriesEvent, each Table.RowCount([EventTable_ChangedType]) > 0),
Ad_Data = Table.AddColumn(FilteredRows, "Data", each Table.SelectRows([EventTable_ChangedType], (x)=> x[Time] >= [T200] and x[Time] <= [T300]), type table),
ExpandedData = Table.ExpandTableColumn(Ad_Data, "Data", {"MR-DC DRB PDCP DL Throughput Total (kbps)", "MR-DC DRB PDCP UL Throughput Total (kbps)"}, {"MR-DC DRB PDCP DL Throughput Total (kbps)", "MR-DC DRB PDCP UL Throughput Total (kbps)"}),
FilteredRows2 = Table.SelectRows(ExpandedData, each ([#"MR-DC DRB PDCP DL Throughput Total (kbps)"] <> null)),
RemovedColumns = Table.RemoveColumns(FilteredRows2,{"EventTable_ChangedType"})
in
RemovedColumnsHi dufoq,
I was not intentionally being lazy.
Origionally I put the concept I was trying to explain in my origional post.
What I was looking for is an approach to filter rows based on data in another table.
I.e. start and end times in one table and the events listed by time in another.
I did try to share the source data using OndDrive or Dropbox, both of which need a corresponding email to allow permissions to access.
The system_files in question have over 700,000 entries in them with more than 350 Columns.
This is simplified down to explain the concept.
Thank you for your reply I will try to replicate it.
I appricate your time and effort.
Thanks,
V.