Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Fcoatis
Post Patron
Post Patron

Power Query Group By

Hello,

I am transforming a bunch of PDF files and I get to the point where I need to extract the last table of each file. I tried to use Group By by the max of column "LastTable"

 

Screenshot 2021-02-22 120140.png

But its not returning the records marked as yellow. Any help would be appreciated.

 

Thanks

1 ACCEPTED SOLUTION

It's not as efficient, but you can do it in a single filter step like this

 

= Table.SelectRows(
      #"Prev Step",
      (x) => x[LastTable]
          = List.Max(
                Table.SelectRows(#"Changed Type", each [Name] = x[Name])[LastTable]
            )
  )

 

It's doing essentially the same thing, i.e., checking if [LastTable] equals the maximal [LastTable] index over the sub-table that has a matching [Name].

View solution in original post

5 REPLIES 5
AlexisOlson
Super User
Super User

Try a Group By on [Name] with max over [LastTable] as a separate query and then do a merge on the table in your screenshot using an inner join (and matching on both columns) to use the new separate query as a filter.

@AlexisOlson  Yes . I did that ( and it worked) . But the goal was to try just one query, once the whole project has many queries and functions.

 

Thank you anyway

It's not as efficient, but you can do it in a single filter step like this

 

= Table.SelectRows(
      #"Prev Step",
      (x) => x[LastTable]
          = List.Max(
                Table.SelectRows(#"Changed Type", each [Name] = x[Name])[LastTable]
            )
  )

 

It's doing essentially the same thing, i.e., checking if [LastTable] equals the maximal [LastTable] index over the sub-table that has a matching [Name].

Thank you @AlexisOlson!

 

It worked

PC2790
Community Champion
Community Champion

Hi @Fcoatis ,

 

Did you try Advanced grouping based on 'Name' and 'Data' columns and then creating a new column with Operation as Max and column as Last Table?

It should give you the records having the maximum MaxTable value in the specific category.

If this doesn't work for you or your requirement is not purposed, please share Power BI file removing all the sensitive data if any.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.