How to Retrieve Corresponding Value Based on Selected Time in Power BI

[Kedar_Pande]

Table 1: Contains a "Time" column (e.g., 00:00, 00:30, etc.). Table 2: Contains a "MessageCount" column, where data is recorded as semi-hourly values separated by '|'. For example, the MessageCount for a particular timestamp might look like this:

'23|45|12|8|34|15|67|89|44|56|78|90|1|2|3|4|5|6|7|8|9|10|11|12|13|14|15|16|17|18|19|20|21|22|23|24|25|26|27|28|29|30|31|32|33|34|35|36|37|38|39|40|41|42|43|44|45|46|47|48'

Each value in the "MessageCount" column corresponds to a specific time of day, starting from 00:00 (first value) to 23:30 (last value), with each value separated by '|'.

I also have a filter based on the "Time" column from Table 1, which allows users to select a specific time slot (e.g., 00:30).

Problem:

When a user selects a time from Table 1 (e.g., "00:30"), I want to retrieve the corresponding value from the "MessageCount" column in Table 2. For instance, if the selected time is "00:30", I need to extract the value corresponding to the second position in the "MessageCount" column (e.g., the value 45 , 2nd in list)

How can I match the selected time in Table 1 to the appropriate value from Table 2? The data in the "MessageCount" column is semi-hourly, and the goal is to extract the correct message count based on the selected time.

Table 1 structure:

Time (e.g., 00:00, 00:30, etc.)

Table 2 structure:

PartitionKey
RowKey
Timestamp
MessageCount (semi-hourly data in the format 0|0|153|160|168|376|...)

@danextian @Bibiano_Geraldo @Ritaf1983 @johnt75 @Ashish_Mathur @DataNinja777 @ryan_mayu 

[lbendlin]

Use Text.Split and List.Zip  to refactor your data into usable format.

let
    Source = List.Transform({0..47},each _ * 30 / 1440),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type time}}),
    Messages = Text.Split("23|45|12|8|34|15|67|89|44|56|78|90|1|2|3|4|5|6|7|8|9|10|11|12|13|14|15|16|17|18|19|20|21|22|23|24|25|26|27|28|29|30|31|32|33|34|35|36|37|38|39|40|41|42|43|44|45|46|47|48","|"),
    Result = List.Zip({#"Changed Type"[Column1],Messages}),
    #"Converted to Table1" = Table.FromList(Result, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Extracted Values" = Table.TransformColumns(#"Converted to Table1", {"Column1", each Text.Combine(List.Transform(_, Text.From), "|"), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Column1", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"timestamp", "value"})
in
    #"Split Column by Delimiter"

Note that your sample string has more than 48 values.