Forum Discussion

CostaBella's avatar
CostaBella
Frequent Visitor
4 years ago

Group By

I am fairly new to Power Query but am learning quickly. I have a data set of lots bidders that shows the ID# for each proposal, ID# for each bidder, and then each bid. My goal is to pull the lowest bid for each proposal, which I have been able to figure out. However when I use the Group By feature, it takes away the column for the bidder ID#. Any suggestions?

Need Help

6 Replies

  • Einomi's avatar
    Einomi
    Helper V
    4 years ago

    I have replicated some of your data

    Please try the code below and adjust with the name of your columns

     

    let
  Source = Excel.Workbook(File.Contents("/Users/daniel/Desktop/Classeur3.xlsx"), null, true),
  #"Navigation 1" = Source{[Item = "Tableau1", Kind = "Table"]}[Data],
  #"Type de colonne changé" = Table.TransformColumnTypes(#"Navigation 1", {{"Prop ID", Int64.Type}, {"Bider ID", Int64.Type}, {"Bid", Int64.Type}}),
  #"Lignes groupées" = Table.Group(#"Type de colonne changé", {"Prop ID"}, {{"All", each Table.Min( _,"Bid"),type record}})[All],
  Personnalisé =  Table.FromRecords (#"Lignes groupées")
in
  Personnalisé
    • CostaBella's avatar
      CostaBella
      Frequent Visitor
      4 years ago

      Left is before the "Group By" and the right is afterwards.

       

       

       

  • danishefa's avatar
    danishefa
    Frequent Visitor
    4 years ago

    Can you please select the columns ID# proposal and also ID# bider and then select group by

    • CostaBella's avatar
      CostaBella
      Frequent Visitor
      4 years ago

      Yea I tried that, but it still has all the bids for each proposal and bidder. I want it to only show the lowest bid while still mainting the ID#s for proposals and bidders.

       

      Pro ID        Bidder ID             Bid Amount

       

  • ronrsnfld's avatar
    ronrsnfld
    Super User
    4 years ago

    Hard to be specific without sample data, but you could add an aggregation to the Table.Group function which outputs the bidder ID.  You would do this in the Advanced Editor.  Something like {"Bidder ID", each [Bidder ID #]{0}, type text}