Hi folks, I'm hoping someone can help. I need to create a virtual table (from two separate tables of data) that shows who has both viewed AND rated a distinct knowledge article within any 12-hour period. The first table contains a list of article view datestamps, who has viewed the article, and the article ID number. The second table contains the article rating datestamps, who has rated the article, and the article ID number. Here's examples of the tables I'm working with: Table = ArticlesViewed article viewed date (dd/mm/yy) article viewer article id10/10/20 4:00 PMJimmy Jimson12345610/10/20 3:00 PMJimmy Jimson12345110/10/20 3:00 PMJimmy Jimson12345710/10/20 1:00 PMJimmy Jimson12345510/10/20 3:00 PMJimmy Jimson12345717/10/20 10:00 AMJimmy Jimson12390910/10/20 3:00 PMJimmy Jimson12345415/10/20 3:00:00 PMJimmy Jimson12345718/10/20 4:00:00 PMmarky markson12378919/10/20 4:00:00 PMmarky markson12378918/10/20 4:00:00 PMmarky markson12379010/10/20 1:05 PMmarky markson12345610/10/20 1:09 PMmarky markson123457 Table = ArticlesRated article rated date (dd/nn/yy) article rater article ID12/10/20 4:00 PMjimmy jimson12345611/10/20 2:00 AMjimmy jimson12345610/10/20 4:00 PMjimmy jimson12345810/10/20 5:00 PMjimmy jimson12345610/10/20 1:00 PMJimmy Jimson12345416/10/2020 2:00:00 AMjimmy jimson12345618/10/20 4:00:00 PMmarky markson12379118/10/20 4:01:00 PMmarky markson12378920/10/20 8:00 AMmarky markson123789 So if a user views an article and doesn't rate it, that won't be included. But if a user views and rates a particular article in a 12-hour period, then the SAME user views and rates the SAME article in the NEXT 12-hour period (the next 12-hour period starts from the timestamp of the last time that the user rated the article), then that should be included as well. In the live data source there will be 100s of users viewing and\or rating articles. There will be many cases where someone has viewed an article and not rated it, but no cases where someone has rated an article without viewing it first.Here's an example of the virtual table I'd like to end up with: user name article id article viewed and rated within 12 hours?jimmy jimson123456yesjimmy jimson123456yesjimmy jimson123456yesmarky markson123789yesJimmy Jimson123454yes Any help would be greatly appreciated 🙂

Forum Discussion

Helper V

5 years ago

Solved

Create a virtual table based on timestamp data from two other tables

Hi folks, I'm hoping someone can help. I need to create a virtual table (from two separate tables of data) that shows who has both viewed AND rated a distinct knowledge article within any 12-hour period.

The first table contains a list of article view datestamps, who has viewed the article, and the article ID number. The second table contains the article rating datestamps, who has rated the article, and the article ID number. Here's examples of the tables I'm working with:

Table = ArticlesViewed

article viewed date (dd/mm/yy)	article viewer	article id
10/10/20 4:00 PM	Jimmy Jimson	123456
10/10/20 3:00 PM	Jimmy Jimson	123451
10/10/20 3:00 PM	Jimmy Jimson	123457
10/10/20 1:00 PM	Jimmy Jimson	123455
10/10/20 3:00 PM	Jimmy Jimson	123457
17/10/20 10:00 AM	Jimmy Jimson	123909
10/10/20 3:00 PM	Jimmy Jimson	123454
15/10/20 3:00:00 PM	Jimmy Jimson	123457
18/10/20 4:00:00 PM	marky markson	123789
19/10/20 4:00:00 PM	marky markson	123789
18/10/20 4:00:00 PM	marky markson	123790
10/10/20 1:05 PM	marky markson	123456
10/10/20 1:09 PM	marky markson	123457

Table = ArticlesRated

article rated date (dd/nn/yy)	article rater	article ID
12/10/20 4:00 PM	jimmy jimson	123456
11/10/20 2:00 AM	jimmy jimson	123456
10/10/20 4:00 PM	jimmy jimson	123458
10/10/20 5:00 PM	jimmy jimson	123456
10/10/20 1:00 PM	Jimmy Jimson	123454
16/10/2020 2:00:00 AM	jimmy jimson	123456
18/10/20 4:00:00 PM	marky markson	123791
18/10/20 4:01:00 PM	marky markson	123789
20/10/20 8:00 AM	marky markson	123789

So if a user views an article and doesn't rate it, that won't be included. But if a user views and rates a particular article in a 12-hour period, then the SAME user views and rates the SAME article in the NEXT 12-hour period (the next 12-hour period starts from the timestamp of the last time that the user rated the article), then that should be included as well.

In the live data source there will be 100s of users viewing and\or rating articles. There will be many cases where someone has viewed an article and not rated it, but no cases where someone has rated an article without viewing it first.

Here's an example of the virtual table I'd like to end up with:

user name	article id	article viewed and rated within 12 hours?
jimmy jimson	123456	yes
jimmy jimson	123456	yes
jimmy jimson	123456	yes
marky markson	123789	yes
Jimmy Jimson	123454	yes

Any help would be greatly appreciated 🙂

Need Help

lbendlin

5 years ago

Here is an - admittedly crude - approach. Since "rated" events require a "viewed" parent event you can merge both tables and then check the time difference for falling into your 12 hr window.

Table ArticlesViewed:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTQByIjAwUTKwMDhQBfJR0lr8zc3EoFIFmcnwfkGhoZm5iaKcXqICk2xqvYkBTF5qiKDfEqNiXDZHOYyQYg1Y7YVVsaWJJitAlEsSlUMVg1MW6xgGkwQdaQm1iUXakAIuE6zC2g7rEkWQfxdlgaYIS9KU7VGGkAqNoSj2qgj2MB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"article viewed date (dd/mm/yy)" = _t, #" article viewer" = _t, #" article id" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Timestamp", each DateTime.From([#"article viewed date (dd/mm/yy)"],"en-gb")),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Timestamp", type datetime}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {" article viewer", " article id"}, ArticlesRated, {" article rater", " article ID"}, "ArticlesRated", JoinKind.LeftOuter),
    #"Renamed Columns" = Table.RenameColumns(#"Merged Queries",{{"Timestamp", "viewed"}}),
    #"Expanded ArticlesRated" = Table.ExpandTableColumn(#"Renamed Columns", "ArticlesRated", {"article rated date (dd/nn/yy)", "Timestamp"}, {"article rated date (dd/nn/yy)", "Timestamp"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Expanded ArticlesRated",{{"Timestamp", "rated"}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns1",{" article viewer", " article id", "viewed", "rated"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Other Columns", "inWindow", each [rated]>[viewed] and [rated]-[viewed]<=#duration(0,12,0,0))
in
    #"Added Custom1"

Table ArticlesRated:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTSNzTQNzJQMLEyMFAI8FXSUcrKzM2tVACSxfl5QK6hkbGJqZlSrA5QsSFUsRFIsSMBxQZEmWyBqtiUGGdgKvYCK/ZCUWwCUWwGUQx1NTEOt0ByONyG3MSi7EoFEAnXYG5piKHBEK8GC0uwBiOYByzgzsGlOhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"article rated date (dd/nn/yy)" = _t, #" article rater" = _t, #" article ID" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Timestamp", each DateTime.From([#"article rated date (dd/nn/yy)"],"en-gb")),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Timestamp", type datetime}})
in
    #"Changed Type"

(I had to add a timestamp column to be able to work with your date format)

7 Replies

lbendlin

Super User

5 years ago

Here is an - admittedly crude - approach. Since "rated" events require a "viewed" parent event you can merge both tables and then check the time difference for falling into your 12 hr window.

Table ArticlesViewed:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTQByIjAwUTKwMDhQBfJR0lr8zc3EoFIFmcnwfkGhoZm5iaKcXqICk2xqvYkBTF5qiKDfEqNiXDZHOYyQYg1Y7YVVsaWJJitAlEsSlUMVg1MW6xgGkwQdaQm1iUXakAIuE6zC2g7rEkWQfxdlgaYIS9KU7VGGkAqNoSj2qgj2MB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"article viewed date (dd/mm/yy)" = _t, #" article viewer" = _t, #" article id" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Timestamp", each DateTime.From([#"article viewed date (dd/mm/yy)"],"en-gb")),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Timestamp", type datetime}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {" article viewer", " article id"}, ArticlesRated, {" article rater", " article ID"}, "ArticlesRated", JoinKind.LeftOuter),
    #"Renamed Columns" = Table.RenameColumns(#"Merged Queries",{{"Timestamp", "viewed"}}),
    #"Expanded ArticlesRated" = Table.ExpandTableColumn(#"Renamed Columns", "ArticlesRated", {"article rated date (dd/nn/yy)", "Timestamp"}, {"article rated date (dd/nn/yy)", "Timestamp"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Expanded ArticlesRated",{{"Timestamp", "rated"}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns1",{" article viewer", " article id", "viewed", "rated"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Other Columns", "inWindow", each [rated]>[viewed] and [rated]-[viewed]<=#duration(0,12,0,0))
in
    #"Added Custom1"

Table ArticlesRated:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTSNzTQNzJQMLEyMFAI8FXSUcrKzM2tVACSxfl5QK6hkbGJqZlSrA5QsSFUsRFIsSMBxQZEmWyBqtiUGGdgKvYCK/ZCUWwCUWwGUQx1NTEOt0ByONyG3MSi7EoFEAnXYG5piKHBEK8GC0uwBiOYByzgzsGlOhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"article rated date (dd/nn/yy)" = _t, #" article rater" = _t, #" article ID" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Timestamp", each DateTime.From([#"article rated date (dd/nn/yy)"],"en-gb")),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Timestamp", type datetime}})
in
    #"Changed Type"

(I had to add a timestamp column to be able to work with your date format)

MichaelHutchens
Helper V
5 years ago
Thanks so much lbendlin , that works perfectly 🙂 Really appreciate your time here 🙂

lbendlin
Super User
5 years ago
Where is the article ID in your sample data? Is it just one article for all viewers/raters?
- MichaelHutchens
  Helper V
  5 years ago
  Hi lbendlin , I've updated the table column names to hopefully make the IDs clearer. I'm not sure I understand the second part of your question sorry. Each row in the virtual table should represent a single instance in any 12 hour period where a unique person has viewed and rated a unique article - does that help?
lbendlin
Super User
5 years ago
your sample data is inconsistent. Check article 123454. it has been rated two hours before it was viewed. Please provide sample data that accurately describes your situation.
- MichaelHutchens
  Helper V
  5 years ago
  Apologies lbendlin , I've corrected the sample results data now 🙂

v-deddai1-msft

Community Support

5 years ago

Hi MichaelHutchens ,

You can create the following two calculated column:

Article rate date =
CALCULATE (
    MIN ( ArticlesRated[article rated date] ),
    FILTER (
        ArticlesRated,
        ArticlesRated[ article rater] = EARLIER ( ArticlesViewed[ article viewer] )
            && ArticlesRated[ article ID] = EARLIER ( ArticlesViewed[ article id] )
            && ArticlesRated[article rated date]
                >= EARLIER ( ArticlesViewed[Article view date] )
    )
)


article viewed and rated within 12 hours =
IF (
    ISBLANK ( ArticlesViewed[Article rate date] ),
    BLANK (),
    IF (
        VAR a =
            CALCULATE (
                MAX ( ArticlesViewed[Article view date] ),
                FILTER (
                    ArticlesViewed,
                    ArticlesViewed[ article id] = EARLIER ( ArticlesViewed[ article id] )
                        && ArticlesViewed[ article viewer] = EARLIER ( ArticlesViewed[ article viewer] )
                        && ArticlesViewed[Article view date]
                            <= EARLIER ( ArticlesViewed[Article view date] )
                )
            )
        VAR b =
            CALCULATE (
                MAX ( ArticlesViewed[Article rate date] ),
                FILTER (
                    ArticlesViewed,
                    ArticlesViewed[ article id] = EARLIER ( ArticlesViewed[ article id] )
                        && ArticlesViewed[ article viewer] = EARLIER ( ArticlesViewed[ article viewer] )
                        && ArticlesViewed[Article view date] = a
                )
            )
        RETURN
            IF ( ISBLANK ( b ), 0, b >= ArticlesViewed[Article view date] )
                && HOUR ( ArticlesViewed[Article rate date] - ArticlesViewed[Article view date] ) <= 12,
        "Yes",
        BLANK ()
    )
)

For more details, please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EWJNnFy-MvdAkH7YQGwea1oB6vXaRpBC0SwQc27XxiYUOg?e=mGyk49

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

Forum Discussion

Create a virtual table based on timestamp data from two other tables

7 Replies

Related content

Fabric virtual table

GA availability for streaming connector virtual network

Create Measure From Virtual Table

Power Virtual Agent

datetime2 shows timestamp