Forum Discussion

XELANAMYT's avatar
XELANAMYT
Frequent Visitor
1 year ago
Solved

Finding the first record in the latest group

I have a set of data which can be grouped by a Category for each Object, however I need to find the latest grouping, currently I can narrow it down to all the groups, but not the latest. I appreciate...
Need Help
  • AmiraBedh's avatar
    AmiraBedh
    1 year ago

    Hello !

    Thank you for posting on MS Fabric community.

    This is the dataset I used as an example :

     

    If you want to use Power Query, I detailed the major steps in this code :

    let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtJRMgRix4KCnFSlWB2YmBEQO+UkJmcnpRYVVSJJGOOSMAFi59Lk0lygBJKwKS71ZrgkzIHYJTG3OD8PLOji6obhRoiYEUIsFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ObjectId = _t, Revision = _t, Category = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ObjectId", type text}, {"Revision", Int64.Type}, {"Category", type text}}),

    /* Sort by ObjectId then Revision so rows are in logical order */
    #"Sorted Rows" =
        Table.Sort(#"Changed Type", {{"ObjectId", Order.Ascending}, {"Revision", Order.Ascending}}),

    /* Add an Index to compare each row with the previous one */
    #"Added Index"           = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type),
    #"Added PrevIndex"       = Table.AddColumn(#"Added Index", "PrevIndex", each [Index]-1, Int64.Type),

    /* Pull the previous row Category & ObjectId */
    #"Merged PrevRow" =
        Table.NestedJoin(
            #"Added PrevIndex",
            {"PrevIndex"},
            #"Added Index",
            {"Index"},
            "PrevRow",
            JoinKind.LeftOuter
        ),
    #"Expanded PrevRow" =
        Table.ExpandTableColumn(
            #"Merged PrevRow",
            "PrevRow",
            {"ObjectId", "Category"},
            {"PrevObjectId", "PrevCategory"}
        ),

    /* Flag the start of a new contiguous group (1 = new group) */
    #"Add GroupStart" =
        Table.AddColumn(
            #"Expanded PrevRow",
            "GroupStart",
            each if [ObjectId] = [PrevObjectId] and [Category] = [PrevCategory] then 0 else 1,
            Int64.Type
        ),

    /* Create a running GroupID per ObjectId */
    #"Add GroupID" =
        Table.AddColumn(
            #"Add GroupStart",
            "GroupID",
            each
                List.Sum(
                    Table.SelectRows(
                        #"Add GroupStart",
                        (r) => r[ObjectId] = [ObjectId] and r[Index] <= [Index]
                    )[GroupStart]
                ),
            Int64.Type
        ),

    /*  For every ObjectId, keep only rows that belong to the latest GroupID */
    LatestGroupPerObject =
        let
            MaxGroups =
                Table.Group(
                    #"Add GroupID",
                    {"ObjectId"},
                    {{"MaxGroupID", each List.Max([GroupID]), Int64.Type}}
                )
        in
            Table.NestedJoin(
                #"Add GroupID",
                {"ObjectId", "GroupID"},
                MaxGroups,
                {"ObjectId", "MaxGroupID"},
                "J",
                JoinKind.Inner
            ),

    /* Within each latest-group keep only the first Revision */
    #"Keep First of Each Latest" =
        Table.SelectRows(
            Table.Sort(LatestGroupPerObject, {{"Revision", Order.Ascending}}),
            (row) =>
                row[Revision]
                    = List.Min(
                        Table.SelectRows(
                            LatestGroupPerObject,
                            (r) => r[ObjectId] = row[ObjectId] and r[GroupID] = row[GroupID]
                        )[Revision]
                    )
        ),
/*  Finally keep just the three original columns */
    Output =
        Table.SelectColumns(#"Keep First of Each Latest", {"ObjectId", "Revision", "Category"})
in
    Output

    The output is :

     

    If you want the solution in DAX, I created 3 calculated columns, the 1st one to flag where a new group starts,

    the 2nd one is to run the GroupID per ObjectId and the 3rd one to identify the 1st row of the latest group :

     

    GroupStart = 
VAR PrevCat =
    CALCULATE (
        MAX ( 'Table B'[Category] ),
        FILTER (
            'Table B',
            'Table B'[ObjectId] = EARLIER ( 'Table B'[ObjectId] )
                && 'Table B'[Revision] = EARLIER ( 'Table B'[Revision] ) - 1
        )
    )
RETURN IF ( PrevCat = 'Table B'[Category], 0, 1 )



GroupID = 
CALCULATE (
    SUM ( 'Table B'[GroupStart] ),
    FILTER (
        'Table B',
        'Table B'[ObjectId] = EARLIER ( 'Table B'[ObjectId] )
            && 'Table B'[Revision] <= EARLIER ( 'Table B'[Revision] )
    )
)


IsFirstOfLatest = 
VAR LatestGroup =
    CALCULATE ( MAX ( 'Table B'[GroupID] ), ALLEXCEPT ( 'Table B', 'Table B'[ObjectId] ) )
VAR MinRevisionInLatest =
    CALCULATE (
        MIN ( 'Table B'[Revision] ),
        FILTER ( 'Table B', 'Table B'[ObjectId] = EARLIER ( 'Table B'[ObjectId] ) && 'Table B'[GroupID] = LatestGroup )
    )
RETURN
    IF ( 'Table B'[GroupID] = LatestGroup && 'Table B'[Revision] = MinRevisionInLatest, 1, 0 )

     

    In your visual you will need to use IsFirstOfLatest as filter to select only the rows having the value of 1 for that CC :

     

    I attached the pbix file with the solution.

jgeddes's avatar
jgeddes
Icon for Super User rankSuper User
1 year ago

If I understand your requirement, you are looking for an output like this...

You can use GroupKind.Local in the Table.Group function.

let
    Source = 
    Table.FromRows(
        Json.Document(
            Binary.Decompress(
                Binary.FromText("i45WcnRyVtJRMgRix4KCnFSlWB2YmBEQO+UkJmcnpRYVVSJJGOOSMAFi59Lk0lygBJKwKS71ZrgkzIHYJTG3OD8PLOji6obhRoiYEUIsFgA=", BinaryEncoding.Base64), 
                Compression.Deflate
            )
        ), 
        let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ObjectId = _t, Revision = _t, Category = _t]
    ),
    #"Changed Type" = 
    Table.TransformColumnTypes(
        Source,
        {
            {"ObjectId", type text}, 
            {"Revision", Int64.Type}, 
            {"Category", type text}
        }
    ),
    #"Grouped Rows" = 
    Table.Group(
        #"Changed Type", 
        {"ObjectId", "Category"}, 
        {
            {"FirstRevision", each List.Min([Revision]), type nullable number}, 
            {"FirstCategory", each Record.Field(Table.SelectRows(_, (r)=>r[Revision] = List.Min([Revision])){0}, "Category"), type text}
        }, 
        GroupKind.Local
    ),
    #"Removed Columns" = 
    Table.RemoveColumns(
        #"Grouped Rows",
        {"Category"}
    )
in
    #"Removed Columns"

If you are looking for a different output, please post what you are expecting to see from the example data you first posted.

Hope this helps.

XELANAMYT's avatar
XELANAMYT
Frequent Visitor
1 year ago

Hi, I would just be looking for the single row in this example ObjectId=ABC, Revision=5, Category=Blackberry.

 

However assume there are many other ObjectIds which could have similar groupings for Category, and the value for Category can be filtered on too, so 'Blackberry' can't be hardcoded.

 

In fact, the result set you have given would be fine, just without the row ObjectId=ABC, Revision=2, Category=Blackberry

  • jgeddes's avatar
    jgeddes
    Icon for Super User rankSuper User
    1 year ago

    Does this work for you?

    let
    Source = 
    Table.FromRows(
        Json.Document(
            Binary.Decompress(
                Binary.FromText("i45WcnRyVtJRMgRix4KCnFSlWB2YmBEQO+UkJmcnpRYVVSJJGOOSMAFi59Lk0lygBJKwKS71ZrgkzIHYJTG3OD8PLOji6obhRoiYEUIsFgA=", BinaryEncoding.Base64), 
                Compression.Deflate
            )
        ), 
        let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ObjectId = _t, Revision = _t, Category = _t]
    ),
    #"Changed Type" = 
    Table.TransformColumnTypes(
        Source,
        {
            {"ObjectId", type text}, 
            {"Revision", Int64.Type}, 
            {"Category", type text}
        }
    ),
    #"Grouped Rows" = 
    Table.Group(
        #"Changed Type", 
        {"ObjectId", "Category"}, 
        {
            {"FirstRevision", each List.Min([Revision]), type nullable number}
        },
        GroupKind.Local
    ),
    #"Grouped Rows1" = 
    Table.Group(
        #"Grouped Rows", 
        {"ObjectId", "Category"}, 
        {
            {"Revision", each List.Max([FirstRevision]), type nullable number}
        }
    )
in
    #"Grouped Rows1"

     

     

  • AmiraBedh's avatar
    AmiraBedh
    Icon for Super User rankSuper User
    1 year ago

    If you want DAX, you can create a calculated table :

    LatestGroupStartRows =
VAR AddGroupStart =
    ADDCOLUMNS (
        'TableA',
        "GroupStart",
        VAR PrevCat =
            CALCULATE (
                MAX ( 'TableA'[Category] ),
                FILTER (
                    'TableA',
                    'TableA'[ObjectId] = EARLIER ( 'TableA'[ObjectId] )
                        && 'TableA'[Revision] = EARLIER ( 'TableA'[Revision] ) - 1
                )
            )
        RETURN IF ( 'TableA'[Category] = PrevCat, 0, 1 )
    )
VAR AddGroupID =
    ADDCOLUMNS (
        AddGroupStart,
        "GroupID",
        VAR ThisObject = [ObjectId]
        VAR ThisRevision = [Revision]
        RETURN
            COUNTROWS (
                FILTER (
                    AddGroupStart,
                    [ObjectId] = ThisObject
                        && [Revision] <= ThisRevision
                        && [GroupStart] = 1
                )
            )
    )
VAR LatestPerObject =
    ADDCOLUMNS (
        AddGroupID,
        "LatestGroupID",
        CALCULATE (
            MAX ( [GroupID] ),
            ALLEXCEPT ( AddGroupID, [ObjectId] )
        )
    )
RETURN
    FILTER (
        LatestPerObject,
        [GroupID] = [LatestGroupID]
            && [Revision]
                = CALCULATE (
                    MIN ( [Revision] ),
                    FILTER (
                        LatestPerObject,
                        [ObjectId] = EARLIER ( [ObjectId] )
                            && [GroupID] = EARLIER ( [GroupID] )
                    )
                )
    )

     

    If you are going to use PQ :

    let
    
    Source =
        Excel.CurrentWorkbook(){[Name="Source"]}[Content],  
    #"Changed Type" =
        Table.TransformColumnTypes(
            Source,
            {{"ObjectId", type text}, {"Revision", Int64.Type}, {"Category", type text}}
        ),

    
    #"Sorted Rows" =
        Table.Sort(#"Changed Type", {{"ObjectId", Order.Ascending}, {"Revision", Order.Ascending}}),

    
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Idx", 0, 1, Int64.Type),

    
    #"Added PrevRecord" =
        Table.AddColumn(
            #"Added Index",
            "Prev",
            each if [Idx]=0 then null else #"Added Index"{[Idx]-1},
            type any
        ),
    #"Expanded Prev" =
        Table.ExpandRecordColumn(
            #"Added PrevRecord",
            "Prev",
            {"ObjectId", "Category"},
            {"PrevObjectId", "PrevCategory"}
        ),

    
    #"Added GroupStart" =
        Table.AddColumn(
            #"Expanded Prev",
            "GroupStart",
            each if [ObjectId]=[PrevObjectId] and [Category]=[PrevCategory] then 0 else 1,
            Int64.Type
        ),

    
    #"Added GroupID" =
        Table.AddColumn(
            #"Added GroupStart",
            "GroupID",
            each
                List.Sum(
                    Table.SelectRows(
                        #"Added GroupStart",
                        (r) => r[ObjectId]=[ObjectId] and r[Idx] <= [Idx]
                    )[GroupStart]
                ),
            Int64.Type
        ),

    
    LatestGroupID =
        Table.Group(
            #"Added GroupID",
            {"ObjectId"},
            {{"MaxGroupID", each List.Max([GroupID]), Int64.Type}}
        ),

    
    #"Kept Latest Block" =
        Table.NestedJoin(
            #"Added GroupID",
            {"ObjectId", "GroupID"},
            LatestGroupID,
            {"ObjectId", "MaxGroupID"},
            "Join",
            JoinKind.Inner
        ),
    #"Expanded Join" =
        Table.ExpandTableColumn(#"Kept Latest Block", "Join", {}),

    
    #"First Row Of Latest" =
        Table.Group(
            #"Expanded Join",
            {"ObjectId"},
            {
                {"FirstRow",
                 each Table.Min(_, "Revision"),
                 type table [ObjectId=nullable text, Revision=number, Category=text, Idx=number, PrevObjectId=nullable text, PrevCategory=nullable text, GroupStart=number, GroupID=number]
                }
            }
        ),
    #"Expanded FirstRow" =
        Table.ExpandTableColumn(
            #"First Row Of Latest",
            "FirstRow",
            {"ObjectId", "Revision", "Category"},
            {"ObjectId", "Revision", "Category"}
        ),

    
    Output =
        Table.SelectColumns(#"Expanded FirstRow", {"ObjectId", "Revision", "Category"})
in
    Output