cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Fcoatis
Helper V
Helper V

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
Memorable Member
Memorable Member

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].

View solution in original post

Thank you @AlexisOlson!

 

It worked

PC2790
Impactful Individual
Impactful Individual

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
User Groups Public Preview

Join us for our User Group Public Preview!

Power BI User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.