Forum Discussion

smpa01's avatar
smpa01
Community Champion
3 years ago
Solved

Incorporating Group by Max in Table.SelectRows

I have a dataset and I want Table.SelectRows to return the rows that has max fileIndex group by file Name.   I can currently do this let Source = Table.FromRows(Json.Document(Binary.Decomp...
Need Help
  • ronrsnfld's avatar
    ronrsnfld
    3 years ago

    Group and filter:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSjNU0lEC4WinzLzEospYpVgdqKgRVlFjDFEjrCYYYTXBCKcJJiiisQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [fileName = _t, fileIndex = _t, Content = _t]),
    ct = Table.TransformColumnTypes(Source,{{"fileName", type text}, {"fileIndex", Int64.Type}, {"Content", type text}}),

    #"Max fileIndex per filName" = Table.Group(ct,{"fileName"},{
        {"filtered", each Table.FromRecords({Table.Max(_,"fileIndex" )}),
            type table[fileName=text, fileIndex=Int64.Type, Content=text]}
        }),
    #"Expanded filtered" = Table.ExpandTableColumn(#"Max fileIndex per filName", "filtered", {"fileIndex", "Content"})
in
    #"Expanded filtered"

     

     

     

  • AlexisOlson's avatar
    AlexisOlson
    3 years ago

    This might help. Check out my Approach #4 in particular:

    https://community.powerbi.com/t5/Community-Blog/Select-Distinct-Rows-Ordered-by-Another-Column-Power-Query/ba-p/2141505

     

    Here's a version with Table.Join instead of Table.NestedJoin:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSjNU0lEC4WinzLzEospYpVgdqKgRVlFjDFEjrCYYYTXBCKcJJiiisQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [fileName = _t, fileIndex = _t, Content = _t]),
    ct = Table.TransformColumnTypes(Source,{{"fileName", type text}, {"fileIndex", Int64.Type}, {"Content", type text}}),
    #"Grouped Rows" = Table.Group(ct, {"fileName"}, {{"fileIndex", each List.Max([fileIndex]), type nullable number}}),
    #"Merged Queries" = Table.Join(ct, {"fileName", "fileIndex"}, #"Grouped Rows", {"fileName", "fileIndex"}, JoinKind.Inner)
in
    #"Merged Queries"
  • CNENFRNL's avatar
    CNENFRNL
    3 years ago

    In terms of performance, Table.Group() is better. All in all, try to use database to finish this task.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSjNU0lEC4WinzLzEospYpVgdqKgRVlFjDFEjrCYYYTXBCKcJJiiisQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [fileName = _t, fileIndex = _t, Content = _t]),
    ct = Table.TransformColumnTypes(Source,{{"fileName", type text}, {"fileIndex", Int64.Type}, {"Content", type text}}),
    Grouped = Table.Group(ct, "fileName", {"grp", each Table.Max(_, "fileIndex")}),
    #"Expanded grp" = Table.ExpandRecordColumn(Grouped, "grp", {"fileIndex", "Content"}, {"fileIndex", "Content"})
in
    #"Expanded grp"
    -- for SQL Server
WITH cte_rn
     AS (SELECT *
                , Row_number ()
                    OVER (
                      partition BY filename
                      ORDER BY fileindex DESC) AS RN
         FROM   yearly_sales)
SELECT *
FROM   cte_rn
WHERE  rn = 1

     

CNENFRNL's avatar
CNENFRNL
Community Champion
3 years ago

In terms of performance, Table.Group() is better. All in all, try to use database to finish this task.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSjNU0lEC4WinzLzEospYpVgdqKgRVlFjDFEjrCYYYTXBCKcJJiiisQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [fileName = _t, fileIndex = _t, Content = _t]),
    ct = Table.TransformColumnTypes(Source,{{"fileName", type text}, {"fileIndex", Int64.Type}, {"Content", type text}}),
    Grouped = Table.Group(ct, "fileName", {"grp", each Table.Max(_, "fileIndex")}),
    #"Expanded grp" = Table.ExpandRecordColumn(Grouped, "grp", {"fileIndex", "Content"}, {"fileIndex", "Content"})
in
    #"Expanded grp"
-- for SQL Server
WITH cte_rn
     AS (SELECT *
                , Row_number ()
                    OVER (
                      partition BY filename
                      ORDER BY fileindex DESC) AS RN
         FROM   yearly_sales)
SELECT *
FROM   cte_rn
WHERE  rn = 1

 

smpa01's avatar
smpa01
Community Champion
3 years ago

How much I wish that the data already went to a server so that I did not have to use PQ. I have nothing against PQ but it is not scalable.  Datamart is also switched off  (org-level) which is why can't take advantage of that too.

 

While we are still at it, other TSQL to pull the same string

 

declare @t1 as table (fileName int, fileIndex int, cat varchar(max))

insert into @t1

select * from 
(values(1,1,'bin'),(1,2,'bin'),(1,3,'bin'),(2,1,'bin'),(2,2,'bin')) t(a,b,c)

--option#1
select a.fileName, a.fileIndex, a.cat
from @t1 a
where exists 
(select c.fileName, c.fileIndex from 
		(select b.fileName, max(b.fileIndex) as fileIndex from @t1 b group by b.fileName) as c 
			where a.fileName =c.fileName and a.fileIndex=c.fileIndex)

--option#2
select top 1 with ties
a.fileName, a.fileIndex, a.cat
from @t1 a
order by row_number() over (partition by a.fileName order by a.fileIndex desc)