March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Hi All,
I have one master product table in AWS mysql DB. This table has below column and sample values
Index | Product ID | Value |
1 | PRD1 | 20 |
2 | PRD2 | 40 |
3 | PRD3 | 20 |
4 | PRD1 | 50 |
5 | PRD3 | 60 |
6 | PRD2 | 70 |
Now I want to import only unique Product IDs from the data, as you can see there are multiple enteries for the same product. I want to be able to import only those records having max value in the Index column. So my power query should import below records only in my Power BI desktop
Index | Product ID | Value |
4 | PRD1 | 50 |
5 | PRD3 | 60 |
6 | PRD2 |
70 |
Pl. suggest.
Proud to be a Super User!
Solved! Go to Solution.
here a example of the needed statement:
https://www.plus2net.com/sql_tutorial/sql_max.php
Proud to be a Super User!
Hello @negi007
you can use Table.Group to make this in power Query. However you have to check if query folding is talking place, otherwise you have to go for the solution of @StefanoGrimaldi, and directly make a SQL statement. Here a code example
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlRQ0lEKCHIB00YGSrE60UpGUDEwbQIRM4aKGSOpM0HWawoRM0VWZwYRM0M2zxwoFgsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Index " = _t, #"Product ID " = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Index ", Int64.Type}, {"Product ID ", type text}, {"Value", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Product ID "}, {{"MaxValue", each Table.Max(_, "Value"), type record}}),
#"Expanded MaxValue" = Table.ExpandRecordColumn(#"Grouped Rows", "MaxValue", {"Index ", "Value"}, {"Index ", "Value"})
in
#"Expanded MaxValue"
Copy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
hey,
for this you would need to send a sql statement directly to the source to do the handling and get you the filtered data would be the best option here (that option its on the source conection under advanced options sql statement):
using select sql max value by ID statement you would get that result.
if this helped give some kudos and mark as solution for others to find.
Proud to be a Super User!
here a example of the needed statement:
https://www.plus2net.com/sql_tutorial/sql_max.php
Proud to be a Super User!
@StefanoGrimaldi Thank you for guiding me in the right direction. I was able to do what I wanted to achieve. Also got to know how we can use native SQL command in powerquery instead of usual m-query language.
Below is the linkon writing SQL queries in powerquery
my final query was similar to below code
select * from ProdctMaster
where ProductMasterID in (SELECT max(ProductMasterID) FROM
ProductMaster GROUP BY ProductName )
Proud to be a Super User!
grad could help, just keep in mind the more you send over SQL statement the load of that its worked by the datasource not Power BI, so thats a factor to keep in mind when deciding M vs sending query to the source to get the result via SQL.
Proud to be a Super User!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
20 | |
13 | |
10 | |
9 | |
7 |
User | Count |
---|---|
39 | |
27 | |
16 | |
15 | |
10 |