Relationship or Group by?

[TatianaGuihur]

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.

[edhans]

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]

Hi @edhans thank your for answering me.

Here some sample data : 

What I have 

IdCommEcoleNomCommunauteDescrMoyenne de IdAdresseNomComplet

101652	1	classe 530	De la Petite-gare	1633057	Alphonso, Dominick
101652	1	classe 530	De la Petite-gare	881047	Bouchard, Maryse
101652	1	classe 530	De la Petite-gare	1633019	Landry-Allard, Mickael
101652	1	classe 530	De la Petite-gare	1781414	Sekpe, Raphaël
102121	1	Groupe 320	De la Petite-gare	2265672	Andrzejewski, Shelby
102121	1	Groupe 320	De la Petite-gare	766004	Arsenault, Olivier
102121	1	Groupe 320	De la Petite-gare	2577690	Duteau, Vincent
102121	1	Groupe 320	De la Petite-gare	1514764	Lamothe, myriam
102121	1	Groupe 320	De la Petite-gare	2377697	Langevin, Nadège
102121	1	Groupe 320	De la Petite-gare	1046453	Moles, Daphnée
102121	1	Groupe 320	De la Petite-gare	1514775	Monette, Mélodie
102121	1	Groupe 320	De la Petite-gare	1430343	Pasqualini, Laetitia
102121	1	Groupe 320	De la Petite-gare	1167624	Porrovecchio, Clémence
102121	1	Groupe 320	De la Petite-gare	456254	Ratelle, Jeanne
102317	1	025-410-Mme Lyne	De la Petite-gare	2651901	Briand, Chloé
102317	1	025-410-Mme Lyne	De la Petite-gare	523315	Desbiens, Ronald
102317	1	025-410-Mme Lyne	De la Petite-gare	523342	Plamondon, Lyne
102317	1	025-410-Mme Lyne	De la Petite-gare	2651923	Tang, Amy

What I should get is one row by IdComm 

IdCommEcoleNomCommunauteDescrMoyenne de IdAdresseNomComplet

101652	1	classe 530	De la Petite-gare	881047	Bouchard, Maryse
102121	1	Groupe 320	De la Petite-gare	456254	Ratelle, Jeanne
102317	1	025-410-Mme Lyne	De la Petite-gare	523315	Desbiens, Ronald

Thank, I appreciate your help.

[edhans · Accepted solution]

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

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 

[edhans]

Great! I use Table.Min and Table.Max all of the time for this purpose.