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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

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.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.