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.

Adresse Minimun

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 by in Power Query...but still dont show me  only one line in my Table Visualisation.

Look at my relationship........Maybe is not good....I did it manually.

 

 

Please advise, Thanks in advance.

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.

5 Replies

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