Need help, How can I get latest date of "Sessiontime" for each user name . while keeping all columns

[besthyde]

Hello everyone,

really appreciate if someone can help me on this question

I have a source table like below, i want to filter or group below table in PBI desktop to only show latest session time and in the mean time.. keep or others columns

What i want is something like below:

[Mariusz · Accepted solution]

Hi @besthyde 

In Power Query Editor, right-click on the Lates Time datapoint and  Select Text Filters > Equals, this will filter your table to this selection.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[V-pazhen-msft · Accepted solution]

@besthyde 

If you want to get to your expected table, you just import the data and then select "Latest Time" in the data view. 

If you want to maintain all data You can just import the data into Power BI desktop, just create a table visual including all columns then filter the [Seession Time] to only show "Latest time". 

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

[BA_Pete]

Hi @besthyde,

In Query Editor, create a new query > Blank Query, go to Advanced Editor, and replace the default code with the following:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddJPb4IwGAbwr7L0bLa2ikJP48+Oyw7zZjgU7QSHLUMQt0+/4szavC8mcIA3vzztk3ezIXEckxlJ7UvnT/bhlNMHNhec2l92+Lx+eV8/pm+v9rMrFclnvmFLZ7igDJuvvtp+AkWDf0UjwZZYFa0ZNMziLisQixCrD3OBhjkTCb7C5tAfG7W7siRJxvnI3AFZKIKR2aHPzFm1ANGVu1UoOMOolj/fEOHSAdqZPTwdRaXDINOq41Wl6VhDMl26Hfqqak5ITZQO1FaelexgWIR6B+ygBlXDMFcG5WLBsNr3ujA4DTcPXC1v6+Qh3DxARV3pv83Isuz+wtuhr2TT1Aqoie6BOjVlpS8wDJcPmDYdzGK4eoCGUt2q9xTeeKA6Wdm1Inn+Cw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [UserName = _t, Office = _t, SessionIdTime = _t, FromUri = _t, Unique = _t]),
    #"Grouped UserName" = Table.Group(Source, {"UserName"}, {{"AllOtherData", each _, type table [UserName=text, Office=text, SessionIdTime=text, FromUri=text, Unique=text]}}),
    #"Added LatestDate" = Table.AddColumn(#"Grouped UserName", "LatestDate", each Table.Max([AllOtherData], "SessionIdTime")),
    #"Expanded LatestDate" = Table.ExpandRecordColumn(#"Added LatestDate", "LatestDate", {"SessionIdTime"}, {"LatestDate.SessionIdTime"}),
    #"Expanded AllOtherData" = Table.ExpandTableColumn(#"Expanded LatestDate", "AllOtherData", {"Office", "SessionIdTime", "FromUri", "Unique"}, {"Office", "SessionIdTime", "FromUri", "Unique"}),
    #"Changed DateTime Types" = Table.TransformColumnTypes(#"Expanded AllOtherData",{{"LatestDate.SessionIdTime", type datetime}, {"SessionIdTime", type datetime}}),
    #"Filtered Latest Date Only" = Table.SelectRows(#"Changed DateTime Types", each ([SessionIdTime] = [LatestDate.SessionIdTime])),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Latest Date Only",{"LatestDate.SessionIdTime"})
in
    #"Removed Columns"

You can then follow the steps I took on my sample dataset to dynamically filter your own.

[Mariusz]

Hi @besthyde 

Are you looking to import only Latest Time data to your model, if so you can filter the data in the Power Query Editor.

If you want to preserve the other data in the model and just filter the visual to "Latest time"

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[besthyde]

Hello @Mariusz 

thanks for the reply

How can i import this table while only get latest session time..  and keep all the columns

[Mariusz · Accepted solution]

Hi @besthyde 

In Power Query Editor, right-click on the Lates Time datapoint and  Select Text Filters > Equals, this will filter your table to this selection.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[BA_Pete]

Hi @besthyde ,

This depends on your source. If you are pulling from an Excel file or similar flat file you need to import all data first, then filter afterwards.

If you are using a database server as a source, you would initially bring all data in and filter, but Power BI would fold your query (https://docs.microsoft.com/en-us/power-query/power-query-folding) back to the server to only import filtered data in subsequent queries.

Let me know if you are using a flat file source and I'll show you how to do the dynamic filter in Power Query Editor.