Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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! 🙂
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
Please, post some sample data