Power Query Group By

[Fcoatis]

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"

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

Thanks

[AlexisOlson]

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.

[Fcoatis]

@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

[AlexisOlson · 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].

[Fcoatis]

Thank you @AlexisOlson!

It worked

[PC2790]

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.