PowerQuery - New Column from another Table

[Chuck1892]

Hello,

I am in the process of creating a datamart. The data used for this is usage data from our website. For example, all new searches of the users are collected in a table (in the query, this is table r). I would like to include the login ID for each of these searches, but this login ID is in another table in the database.
I know that if I were to write this in SQL, the Querry would look like this:

Select max(l.id) from loginlogentry l where l.user_id = r.user_id and l.occurred < r.occurred order by occurred desc limit 1

The structure of table r simplified:
User_id, occured (timestamp of the new search)

The structure of table loginlogentry l simplified:

id, user_id, occured (timestamp of the login)

But I can't really get it to work in M - is it even possible? Would be grateful for help! 🙂

[Peter_Beck]

Here is how (roughly) it might be done. I've broken it down into several steps so you can more clearly see the process

First, I have two tables, which i have named "l" and "r"

Query "l": 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUrKTwKSRgZGxroGQGSoYGhkZWCgFKsTrWSEQ9oYIm2MQ9rEFCxtApQoTszJqcRlvClOBVALzIASJfm5uPSbAyVSEstSMeQNIQ6wwCUPNd4SlzzUA4YGuBSYAg2IBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [id = _t, userid = _t, occurred = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"userid", type text}, {"occurred", type datetime}})
in
#"Changed Type"

Query "r":

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSspPUtJRMjIwMtY1ACJDBUMjK1MDpVgd7FKG5mCp4sScnEoMSWOIvpTEslQMOSOgxlgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [userid = _t, occurred = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"userid", type text}, {"occurred", type datetime}})
in
#"Changed Type"

Now I have created another query I called l_joined_r which joins them together. Note that I must rename the columns because Table.Join requires unique column names:

let
Source = Table.Join(l,"userid",Table.RenameColumns(r,{{"userid","r.userid"},{ "occurred","r.occurred"}}),"r.userid", JoinKind.Inner)
in
Source

Now I filter the result in a new query called "candidate_r_occurred" that removes rows according to the condition you specified:

let
Source = Table.SelectRows(l_joined_r,each [occurred]<[r.occurred])
in
Source

Finally I use Table.Group with the Max function on the columns I want to use:

let
Source = Table.SelectRows(l_joined_r,each [occurred]<[r.occurred]),
#"Grouped Rows" = Table.Group(Source, {"userid"}, {{"maxid", each List.Max([id]), type nullable number}, {"max", each List.Max([r.occurred]), type nullable datetime}})
in
#"Grouped Rows"

I think this is (approximately) what you are after. I feel that the SELECT you are trying to do is doable in M, but I may have some of the details a little off depending on the results you want. This can also be done more concisely by reducing the number of distinct queries, I just wanted to demonstrate the results at each step.

Hope this helps!

Peter

[serpiva64]

Please, post some sample data