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
Have you tried loading your tables as is and connecting them via the data model?
Hi lbendlin,
No I have not.
I do not know how to go about it it.
In particular how do I filter the data based on the start and ends?
Thanks V.
- lbendlin2 years agoSuper User
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- VHalpenny2 years agoHelper I
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.563. 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.