cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
besthyde
Frequent Visitor

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

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

 

微信截图_20200324170639.jpg

What i want is something like below:

 

微信截图_20200324170704.jpg

2 ACCEPTED SOLUTIONS
Mariusz
Community Champion
Community Champion

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

 

View solution in original post

V-pazhen-msft
Community Support
Community Support

@besthyde 

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

latest time.JPG

 

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.

View solution in original post

6 REPLIES 6
V-pazhen-msft
Community Support
Community Support

@besthyde 

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

latest time.JPG

 

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
Super User
Super User

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.



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Mariusz
Community Champion
Community Champion

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

 

 

Hello @Mariusz 

thanks for the reply

 

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

Mariusz
Community Champion
Community Champion

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

 

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.

 



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.