Forum Discussion

micsafdas's avatar
micsafdas
Frequent Visitor
5 years ago
Solved

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...
  • edhans's avatar
    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
        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:

     

    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.

  • Jimmy801's avatar
    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