Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
negi007
Community Champion
Community Champion

Import Max Value Records from Source data

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.




Did I answer your question? Mark my post as a solution!
Appreciate your Kudos



Proud to be a Super User!


Follow me on linkedin

1 ACCEPTED SOLUTION

here a example of  the needed statement: 

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





Did I answer your question? Mark my post as a solution! / Did it help? Give some Kudos!

Proud to be a Super User!




View solution in original post

5 REPLIES 5
Jimmy801
Community Champion
Community Champion

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
Resident Rockstar
Resident Rockstar

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. 

 





Did I answer your question? Mark my post as a solution! / Did it help? Give some Kudos!

Proud to be a Super User!




here a example of  the needed statement: 

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





Did I answer your question? Mark my post as a solution! / Did it help? Give some Kudos!

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 

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 )




Did I answer your question? Mark my post as a solution!
Appreciate your Kudos



Proud to be a Super User!


Follow me on linkedin

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. 





Did I answer your question? Mark my post as a solution! / Did it help? Give some Kudos!

Proud to be a Super User!




Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors