Forum Discussion
Filter to the last Database version (list.max?)
- 5 years ago
I'm not 100% clear what you need micsafdas but look at this:
From that, I can just keep the data that is related to the max value in the version column using this code:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMjCwUNJRAlEKJanFJUqxOlBhc4iwOZowmupYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Version = _t, OP_Name = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Version", Int64.Type}}), MaxValue = List.Distinct( Table.SelectRows( #"Changed Type", each let varMaxItem = List.Max(#"Changed Type"[Version]) in [Version] = varMaxItem )[OP_Name] ), TotalData = Table.SelectRows( #"Changed Type", each List.Contains(MaxValue, [OP_Name]) ) in TotalDataThe MaxValue step finds the value for the OP_Name that is represented by the max value in the Value column.
The TotalData step filters the original table at #Changed Step by the text returned by the MaxValue step.
This is the result:
If that isn't what you need, please provide sample data with a clear explanation of expected output.
How to get good help fast. Help us help you.
How To Ask A Technical Question If you Really Want An Answer
How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model. - 5 years ago
Hello micsafdas
Hope I got you right... not quite clear, what you exactly need
you can group your data by OP_Name and apply a Table.Max on your version-column. After that you expand the record found. Here an example
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMjAwV9JRclSK1aE+1wLIdcLJtYRwYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Version = _t, OP_Name = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Version", Int64.Type}, {"OP_Name", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"OP_Name"}, {{"AllRows", each Table.Max(_,"Version") }}), #"Expanded AllRows" = Table.ExpandRecordColumn(#"Grouped Rows", "AllRows", {"Version"}, {"Version"}) in #"Expanded AllRows"Copy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
Hello micsafdas
Hope I got you right... not quite clear, what you exactly need
you can group your data by OP_Name and apply a Table.Max on your version-column. After that you expand the record found. Here an example
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMjAwV9JRclSK1aE+1wLIdcLJtYRwYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Version = _t, OP_Name = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Version", Int64.Type}, {"OP_Name", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"OP_Name"}, {{"AllRows", each Table.Max(_,"Version") }}),
#"Expanded AllRows" = Table.ExpandRecordColumn(#"Grouped Rows", "AllRows", {"Version"}, {"Version"})
in
#"Expanded AllRows"
Copy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy