Forum Discussion

Helper I

2 years ago

Solved

Select Rows based on Timestamp in one table and Start End Fields on a Second Table

Hi There, I am new to Power BI, so please forgive the basic question. I have three tables - Session Table : Containing Start and End Times - Mapping Table : Containg Operator to File Nam...

Need Help

dufoq3

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

VHalpenny

Helper I

2 years ago

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.

dufoq3

Community Champion

2 years ago

We can map your mapping table with Events table, but you're still missing mapping column in session table:

VHalpenny
Helper I
2 years ago
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.56

Thanks
V.

dufoq3

Community Champion

2 years ago

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

VHalpenny
Helper I
2 years ago
Hi 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.
dufoq3
Community Champion
2 years ago
You can use any other platform for sharing your data (i.e. google drive etc.)
VHalpenny
Helper I
2 years ago
Thanks for that.
I have uploaded the source data to Google Drive, it allows general access.
This is the link.
Source Files

I have also included the text of the sequence I used in Power BI to add the filename as a column to the table containing all the Events. It is called Power BI Query Sequence for Event Table.txt
dufoq3
Community Champion
2 years ago
Operator1_System1.txt is delimited by ";"
Operator1_System2.txt is delimited by " " (space)

Do you have such inconsistend delimiters or it is just a mistake with this sample?
VHalpenny
Helper I
2 years ago
Hi dufoq3,

That was a mistake, sorry.
When I removed the other columns in excel it saved it with the space. I corrected the first file but forgot to do the second one.
my appologies.
dufoq3
Community Champion
2 years ago
What about this? Upload proper dataset again and let me know please...
VHalpenny
Helper I
2 years ago
Sorry I was unaware of this issue. I think excel had modified the dates
I have now re-constructed both files using a script and checked the that date field is normal.

Here is the link.
https://drive.google.com/file/d/1Dsg62N8N9wA7-c8N5DBKmlsIO4SHizEw/view?usp=drive_link

Thanks again for your help.
dufoq3
Community Champion
2 years ago
Make the link public please.
VHalpenny
Helper I
2 years ago
Sorry
It's done now.
https://drive.google.com/file/d/1Dsg62N8N9wA7-c8N5DBKmlsIO4SHizEw/view?usp=sharing

Forum Discussion

Select Rows based on Timestamp in one table and Start End Fields on a Second Table

Related content

Parameter: Fields selected value

Selected field ratio and rank-Top N as slicer

REAL TIME TILES AUTO CONVERT TIMESTAMP TO UTC

Create Calculated Fields based on Field Parameter selected

datetime2 shows timestamp