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

Filter to the last Database version (list.max?)

Hi!

I honestly searched, but couldn't come up with something that I could apply.

 

I have a long list, in which different versions of the same dataset are distinguished by the column OP_Name. What I want is to always have only the latest version loaded into the report.

As I figured the OP_Name column wouldn't be suitable, I created the Version column and would filter for the highest number.

 

Probably super easy, but please help me out. 🙂

 

image001.png

 

Thanks!

2 ACCEPTED SOLUTIONS
edhans
Super User
Super User

I'm not 100% clear what you need @micsafdas but look at this:

edhans_0-1611851745240.png

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
    TotalData

 

The 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:

edhans_1-1611852073225.png

 

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

Jimmy801
Community Champion
Community Champion

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

View solution in original post

4 REPLIES 4
edhans
Super User
Super User

@micsafdas there are probably a number of ways to do it, and I did it the way I did because I was filtering on the text value based on the maximum value in the numeric field. That may not be necessary, and if you just want to filter for the max numeric value, the simple Table.SelectRows() step you have will work. But your original post said you were filtering on the OP_Name field, so that is why I took the approach I did.

 

By the way, please also accept @Jimmy801 post as a solution if it works for you or helped. A thread can have 2+ solutions.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
micsafdas
Frequent Visitor

Thank you both!

First of all: I'm sorry for not being clear on the expected output. I hope to do better next time.

Having said that, I can announce a success. 🙂

 

Although edhans did understand correctly what I wanted to achieve, I wasn't quite sure what to do with the code (although I do think I understand now). I'm more a GUI kinda guy. 😉

It was in fact the reply of Jimmy who inspired me to do it "my way" and using the group function. I do need to give the "Accept solution" to edhans though, sorry Jimmy. 🙂

 

What do I have now:

Expected was in fact that only records with Version=202008 will be left standing.

 

1. I grouped Version using Operation = "All rows".

image002.png

2. I then filtered on Version using List.Max(#Grouped Rows"[Version])

image003.png

3. Expanded the Column again.

 

Thanks for the help! I must say, this community is pretty awesome.

 

EDIT:

Okay, stupid me. Why go through the grouping at all?

The filtered rows step in Line 17 alone does the trick for me. Am I wrong? 

Jimmy801
Community Champion
Community Champion

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

View solution in original post

edhans
Super User
Super User

I'm not 100% clear what you need @micsafdas but look at this:

edhans_0-1611851745240.png

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
    TotalData

 

The 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:

edhans_1-1611852073225.png

 

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Top Kudoed Authors