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
Now you're missing mapping table and mapping column for 2nd table.
Oops!
Sorry, that mapping table I created in excel, it was a mapping between the operator and the filename.
| Operator | File |
| Operator1 | System_File1 |
| Operator1 | System_File2 |
| Operator1 | System_File3 |
| Operator2 | System_File4 |
The filename column I created in Power BI when transforming the csv and the operator name is in the sessions master file.
- dufoq32 years agoCommunity Champion
We can map your mapping table with Events table, but you're still missing mapping column in session table:
- VHalpenny2 years agoHelper I
When I transform the events table in Power BI
It has the equivilent of this
File;Time;MR-DC DRB PDCP DL Throughput Total (kbps);MR-DC DRB PDCP UL Throughput Total (kbps)
System_File1;7/20/2023 07:37:54.500;;
System_File1;7/20/2023 07:37:54.898;;
System_File1;7/20/202System1;3 07:37:55.500;0;0
System_File1;7/20/2023 07:37:56.500;1.72;6.23
System_File1;7/20/2023 07:37:57.500;0;0
System_File1;7/20/2023 07:37:58.500;0;0
System_File1;7/20/2023 07:37:59.500;0;0
System_File1;7/20/2023 07:38:00.500;18.27;17.81
System_File1;7/20/2023 07:38:01.500;0;0
System_File1;7/20/2023 07:38:02.500;32.17;33.57
System_File1;7/20/2023 07:38:03.500;0;0
System_File1;7/20/2023 07:38:04.500;0;16.76
System_File1;7/20/2023 07:38:05.500;16.77;0
System_File1;7/20/2023 07:38:06.500;0;0
System_File1;7/20/2023 07:38:07.500;3.18;3.2
System_File1;7/20/2023 07:38:08.500;0;0
System_File1;7/20/2023 07:38:09.500;16.2;16.77
System_File1;7/20/2023 07:38:10.500;0;0
System_File1;7/20/2023 07:38:11.394;;
System_File1;7/20/2023 07:38:11.500;0;0
System_File1;7/20/2023 07:38:12.500;0;0
System_File1;7/20/2023 07:38:13.500;14803.03;986.3
System_File1;7/20/2023 07:38:14.500;4193.17;174
System_File1;7/20/2023 07:38:15.500;0;0
System_File1;7/20/2023 07:38:16.500;0;0
System_File1;7/20/2023 07:38:17.500;67.33;31.56Thanks
V.- dufoq32 years agoCommunity Champion
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 RemovedColumns