Forum Discussion

negi007's avatar
negi007
Community Champion
5 years ago
Solved

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.

Need Help

5 Replies

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    5 years ago

    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's avatar
    StefanoGrimaldi
    Resident Rockstar
    5 years ago

    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.