Import Max Value Records from Source data

[negi007]

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.

[Jimmy801]

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

[StefanoGrimaldi]

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. 

[StefanoGrimaldi · Accepted solution]

here a example of  the needed statement: 

https://www.plus2net.com/sql_tutorial/sql_max.php 

[negi007]

@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 

https://blog.crossjoin.co.uk/2017/06/11/query-folding-and-writing-your-own-sql-queries-in-power-quer...

my final query was similar to below code

select * from ProdctMaster
where ProductMasterID in (SELECT max(ProductMasterID) FROM
ProductMaster GROUP BY ProductName )

[StefanoGrimaldi]

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.