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
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information or anything not related to the issue or question.
If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
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.
- dufoq32 years agoCommunity Champion
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: