Forum Discussion

TatianaGuihur's avatar
TatianaGuihur
Helper I
6 years ago
Solved

Relationship or Group by?

Hi everyone 😁, I would like to see only one row of the following 4. I need the row with  minimum value of the row IDADRESSE.  So, I need to see only the second row.   I alredy did a Group b...
Need Help
  • edhans's avatar
    edhans
    6 years ago

    See if this works for you. Here is the M code, which you can paste into a blank query using the Advanced Editor. All of that binary gibberish on the first row is the table data you provided. You can click on the gear icon in the query view to see the table. And apologies if I completely butchered the titles. The weren't aligned in the data you provided and I don't speak French. I barely speak English well. 😉

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pZNNbtswEIWvQnhNA/yns3QSoEBht4ZTdBNkwUgDizVFuvpx4Z6oRo+hi3WopN1lUWYhYASOPr7Re/P4uOCMGy0WdMHxqYLreyBaMny5BxIc2cHgB1geXAe5yUjJtMVqHU5Nin2i5D61PvrquHii/4tbrThTmXabxqpxXU3J1nWXHgpYszR+g9XGxbq7LNchvBBRm4NQgrQrrrjC6gGOJ6Bk706Nm37/ZQku+CvrQ5fGExAp3mIJYbSx+eo1qvsJ3+BHf/SUPDQQni8FQGsMY1nbuushujEMlHwO/uyhK5GnrTU38+k4gBsp+epjBXEoYHHNlTVqdqJNQ4M/rr103rUlumTWZV9cPcDZR0o+uXr6dYASZUwZpSVW2xSgx+yioXG6FrHylFbPrAjDgFNup2tItS+iKcmkysp2rv8+uoArRcnG5SbvSoDcWCOyCbvUdekMVdV43Na7MF1bQGsLmEoboTNy7wYIASf+CC7GfyjJ7SuKCb1UnC23LZDNJcJb7hrNb1j+4BbzEXFZ75qQpmsxUAspuZ4P+2cPES3ep+hC/S6iymu7CxjmWCcM4Nz+rplFdvoLJpqSdYvb//QH", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Comm = _t, Ecole = _t, NomCommunaute = _t, Moyenne = _t, Address = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Comm", Int64.Type}, {"Ecole", type text}, {"NomCommunaute", type text}, {"Moyenne", Int64.Type}, {"Address", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"All Rows", each _, type table [ID=number, Comm=number, Ecole=text, NomCommunaute=text, Moyenne=number, Address=text]}}),
    #"Added Min Row Table" = Table.AddColumn(#"Grouped Rows", "Min Row Table", each Table.Min([All Rows], "Moyenne")),
    #"Expanded Min Row Table" = Table.ExpandRecordColumn(#"Added Min Row Table", "Min Row Table", {"Comm", "Ecole", "NomCommunaute", "Moyenne", "Address"}, {"Comm", "Ecole", "NomCommunaute", "Moyenne", "Address"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Min Row Table",{"ID", "Comm", "Ecole", "NomCommunaute", "Moyenne", "Address"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Comm", Int64.Type}, {"Moyenne", Int64.Type}, {"Ecole", type text}, {"NomCommunaute", type text}, {"Address", type text}})
in
    #"Changed Type1"

     

    The resultant table looks like this:

     

     

    What I did was:

    1. Grouped all of the data by the ID column, but in the New Column, I used the All Rows operation, so it created a table per ID.
    2. I then created a custom column with the following formula: Table.Min([All Rows], "Moyenne") - This will extract a single record based on the minimum value of the Moyenne field.
    3. I expanded all fields in that record except the ID field, which already exists in the parent table.
    4. I then cleaned it up by removing the columns no longer needed and re-type'd the data. When you use nested tables, the data type gets lost and usually everything comes back out as type ABC123.
    5. That then gets loaded to the DAX data model to do whatever reporting you need.

    You can see my PBIX file here.

edhans's avatar
edhans
Community Champion
6 years ago

Can you post some sample data (text format) and how you'd like to eliminate or show the "nomComplet" field as that is not unique and if you group by that, it won't consolidate. I would do this with Table.Max on a nested table in PQ, which I (or someone else if they get back faster) will show you, but I'd need the data prefereably for at least 3 different sets of whatever to ensure it works right, and an answer to my question above.

  • TatianaGuihur's avatar
    TatianaGuihur
    Helper I
    6 years ago

    Hi edhans thank your for answering me.

     

    Here some sample data : 

    What I have 

    IdCommEcoleNomCommunauteDescrMoyenne de IdAdresseNomComplet

    1016521classe 530De la Petite-gare1633057Alphonso, Dominick
    1016521classe 530De la Petite-gare881047Bouchard, Maryse
    1016521classe 530De la Petite-gare1633019Landry-Allard, Mickael
    1016521classe 530De la Petite-gare1781414Sekpe, Raphaël
    1021211Groupe 320De la Petite-gare2265672Andrzejewski, Shelby
    1021211Groupe 320De la Petite-gare766004Arsenault, Olivier
    1021211Groupe 320De la Petite-gare2577690Duteau, Vincent
    1021211Groupe 320De la Petite-gare1514764Lamothe, myriam
    1021211Groupe 320De la Petite-gare2377697Langevin, Nadège
    1021211Groupe 320De la Petite-gare1046453Moles, Daphnée
    1021211Groupe 320De la Petite-gare1514775Monette, Mélodie
    1021211Groupe 320De la Petite-gare1430343Pasqualini, Laetitia
    1021211Groupe 320De la Petite-gare1167624Porrovecchio, Clémence
    1021211Groupe 320De la Petite-gare456254Ratelle, Jeanne
    1023171025-410-Mme LyneDe la Petite-gare2651901Briand, Chloé
    1023171025-410-Mme LyneDe la Petite-gare523315Desbiens, Ronald
    1023171025-410-Mme LyneDe la Petite-gare523342Plamondon, Lyne
    1023171025-410-Mme LyneDe la Petite-gare2651923Tang, Amy

     

     

    What I should get is one row by IdComm 

     

    IdCommEcoleNomCommunauteDescrMoyenne de IdAdresseNomComplet

    1016521classe 530De la Petite-gare881047Bouchard, Maryse
    1021211Groupe 320De la Petite-gare456254Ratelle, Jeanne
    1023171025-410-Mme LyneDe la Petite-gare523315Desbiens, Ronald

     

    Thank, I appreciate your help.

    • edhans's avatar
      edhans
      Community Champion
      6 years ago

      See if this works for you. Here is the M code, which you can paste into a blank query using the Advanced Editor. All of that binary gibberish on the first row is the table data you provided. You can click on the gear icon in the query view to see the table. And apologies if I completely butchered the titles. The weren't aligned in the data you provided and I don't speak French. I barely speak English well. 😉

       

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pZNNbtswEIWvQnhNA/yns3QSoEBht4ZTdBNkwUgDizVFuvpx4Z6oRo+hi3WopN1lUWYhYASOPr7Re/P4uOCMGy0WdMHxqYLreyBaMny5BxIc2cHgB1geXAe5yUjJtMVqHU5Nin2i5D61PvrquHii/4tbrThTmXabxqpxXU3J1nWXHgpYszR+g9XGxbq7LNchvBBRm4NQgrQrrrjC6gGOJ6Bk706Nm37/ZQku+CvrQ5fGExAp3mIJYbSx+eo1qvsJ3+BHf/SUPDQQni8FQGsMY1nbuushujEMlHwO/uyhK5GnrTU38+k4gBsp+epjBXEoYHHNlTVqdqJNQ4M/rr103rUlumTWZV9cPcDZR0o+uXr6dYASZUwZpSVW2xSgx+yioXG6FrHylFbPrAjDgFNup2tItS+iKcmkysp2rv8+uoArRcnG5SbvSoDcWCOyCbvUdekMVdV43Na7MF1bQGsLmEoboTNy7wYIASf+CC7GfyjJ7SuKCb1UnC23LZDNJcJb7hrNb1j+4BbzEXFZ75qQpmsxUAspuZ4P+2cPES3ep+hC/S6iymu7CxjmWCcM4Nz+rplFdvoLJpqSdYvb//QH", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Comm = _t, Ecole = _t, NomCommunaute = _t, Moyenne = _t, Address = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Comm", Int64.Type}, {"Ecole", type text}, {"NomCommunaute", type text}, {"Moyenne", Int64.Type}, {"Address", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"All Rows", each _, type table [ID=number, Comm=number, Ecole=text, NomCommunaute=text, Moyenne=number, Address=text]}}),
    #"Added Min Row Table" = Table.AddColumn(#"Grouped Rows", "Min Row Table", each Table.Min([All Rows], "Moyenne")),
    #"Expanded Min Row Table" = Table.ExpandRecordColumn(#"Added Min Row Table", "Min Row Table", {"Comm", "Ecole", "NomCommunaute", "Moyenne", "Address"}, {"Comm", "Ecole", "NomCommunaute", "Moyenne", "Address"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Min Row Table",{"ID", "Comm", "Ecole", "NomCommunaute", "Moyenne", "Address"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Comm", Int64.Type}, {"Moyenne", Int64.Type}, {"Ecole", type text}, {"NomCommunaute", type text}, {"Address", type text}})
in
    #"Changed Type1"

       

      The resultant table looks like this:

       

       

      What I did was:

      1. Grouped all of the data by the ID column, but in the New Column, I used the All Rows operation, so it created a table per ID.
      2. I then created a custom column with the following formula: Table.Min([All Rows], "Moyenne") - This will extract a single record based on the minimum value of the Moyenne field.
      3. I expanded all fields in that record except the ID field, which already exists in the parent table.
      4. I then cleaned it up by removing the columns no longer needed and re-type'd the data. When you use nested tables, the data type gets lost and usually everything comes back out as type ABC123.
      5. That then gets loaded to the DAX data model to do whatever reporting you need.

      You can see my PBIX file here.

      • TatianaGuihur's avatar
        TatianaGuihur
        Helper I
        6 years ago

        It worked!!!! 😀😁

        I pasted your M code but only show me the 3 rows I asked you. But my real table was longuer than that, so I reproduced the 5 steps.

        I have learned about Table.Min.

         

        Thank you so much edhans