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
Hi lbendlin,
I am having issues sharing the data with you. I wanted to give a zip file but both OneDrive and Dopbox ask me to specify an e-mail address.
Instead I will explain the source files with some sample data.
1. There is a sessions master excel file which contains the following information format. (I have reduced the columns and anonymised the data)
| Operator | Sequence | Point of Interest | Infrastructure Type Fine | T200 | T300 |
| Operator1 | 720074625 | Drive | cities | 2023-07-20 07:46:32.579 | 2023-07-20 07:46:39.579 |
| Operator1 | 720075202 | Drive | cities | 2023-07-20 07:52:08.142 | 2023-07-20 07:52:15.142 |
| Operator1 | 720075740 | Drive | cities | 2023-07-20 07:57:46.954 | 2023-07-20 07:57:53.954 |
| Operator1 | 720080315 | Drive | cities | 2023-07-20 08:03:22.375 | 2023-07-20 08:03:29.375 |
| Operator1 | 720080941 | Drive | cities | 2023-07-20 08:09:48.694 | 2023-07-20 08:09:55.694 |
| Operator1 | 720083755 | Drive | cities | 2023-07-20 08:38:02.599 | 2023-07-20 08:38:09.599 |
| Operator1 | 720084334 | Drive | cities | 2023-07-20 08:43:40.796 | 2023-07-20 08:43:47.796 |
| Operator1 | 720084912 | Drive | cities | 2023-07-20 08:49:19.291 | 2023-07-20 08:49:26.291 |
| Operator1 | 720085459 | Drive | cities | 2023-07-20 08:55:05.787 | 2023-07-20 08:55:12.787 |
| Operator1 | 720090048 | Drive | cities | 2023-07-20 09:00:54.729 | 2023-07-20 09:01:01.729 |
| Operator1 | 720090618 | Drive | cities | 2023-07-20 09:06:25.142 | 2023-07-20 09:06:32.142 |
| Operator1 | 720091154 | Drive | cities | 2023-07-20 09:12:01.161 | 2023-07-20 09:12:08.161 |
| Operator1 | 720091737 | Drive | cities | 2023-07-20 09:17:44.544 | 2023-07-20 09:17:51.544 |
| Operator1 | 720092309 | Drive | cities | 2023-07-20 09:23:16.070 | 2023-07-20 09:23:23.070 |
| Operator1 | 720092843 | Drive | cities | 2023-07-20 09:28:50.806 | 2023-07-20 09:28:57.806 |
| Operator1 | 720093424 | Drive | cities | 2023-07-20 09:34:30.959 | 2023-07-20 09:34:37.959 |
| Operator1 | 720094013 | Drive | cities | 2023-07-20 09:40:19.849 | 2023-07-20 09:40:26.849 |
| Operator1 | 720094726 | Drive | cities | 2023-07-20 09:47:33.674 | 2023-07-20 09:47:40.674 |
| Operator1 | 720095301 | Drive | cities | 2023-07-20 09:53:08.537 | 2023-07-20 09:53:15.537 |
| Operator1 | 720095852 | Drive | cities | 2023-07-20 09:59:00.670 | 2023-07-20 09:59:07.670 |
| Operator1 | 720100440 | Drive | cities | 2023-07-20 10:04:48.336 | 2023-07-20 10:04:55.336 |
| Operator1 | 720101034 | Drive | cities | 2023-07-20 10:10:41.640 | 2023-07-20 10:10:48.640 |
| Operator1 | 720101615 | Drive | cities | 2023-07-20 10:16:22.645 | 2023-07-20 10:16:29.645 |
2. There is a directory which holds csv files with the follwing format (again I have simplified the columns).
Time;MR-DC DRB PDCP DL Throughput Total (kbps);MR-DC DRB PDCP UL Throughput Total (kbps)
7/20/2023 07:37:54.500;;
7/20/2023 07:37:54.898;;
7/20/2023 07:37:55.500;0;0
7/20/2023 07:37:56.500;1.72;6.23
7/20/2023 07:37:57.500;0;0
7/20/2023 07:37:58.500;0;0
7/20/2023 07:37:59.500;0;0
7/20/2023 07:38:00.500;18.27;17.81
7/20/2023 07:38:01.500;0;0
7/20/2023 07:38:02.500;32.17;33.57
7/20/2023 07:38:03.500;0;0
7/20/2023 07:38:04.500;0;16.76
7/20/2023 07:38:05.500;16.77;0
7/20/2023 07:38:06.500;0;0
7/20/2023 07:38:07.500;3.18;3.2
7/20/2023 07:38:08.500;0;0
7/20/2023 07:38:09.500;16.2;16.77
7/20/2023 07:38:10.500;0;0
7/20/2023 07:38:11.394;;
7/20/2023 07:38:11.500;0;0
7/20/2023 07:38:12.500;0;0
7/20/2023 07:38:13.500;14803.03;986.3
7/20/2023 07:38:14.500;4193.17;174
7/20/2023 07:38:15.500;0;0
7/20/2023 07:38:16.500;0;0
7/20/2023 07:38:17.500;67.33;31.56
3. Unfortunately I cannot provide a sample of the required output because it is part of a larger process. However what I am looking for is to file out the events in the individual sessions in the csv files using the time window defined in the sessions mater excel file, (T200 : Start Time - T300 : End Time). This information should form a new table witht the same information as in the csv file, but excluding times outside the defined session windows.
Thanks for your help,
V.
Now you're missing mapping table and mapping column for 2nd table.
- VHalpenny2 years agoHelper I
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.