Forum Discussion

Fcoatis's avatar
Fcoatis
Post Patron
5 years ago
Solved

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"   But its n...
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    5 years ago

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

AlexisOlson's avatar
AlexisOlson
Super User
5 years ago

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's avatar
Fcoatis
Post Patron
5 years ago

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's avatar
    AlexisOlson
    Super User
    5 years ago

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