Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 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.
Solved! Go to 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:
You can see my PBIX file here.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCan 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi @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.
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:
You can see my PBIX file here.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingIt 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
Great! I use Table.Min and Table.Max all of the time for this purpose.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
147 | |
107 | |
105 | |
87 | |
61 |