Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
10 years ago
Solved

Show most recent data

Going off this example: http://community.powerbi.com/t5/Desktop/Comparing-Sales-Orders-rows-and-keeping-the-highest-version/td-p/12729 I have the same issues except I have an extra column: ...
Need Help
Tips and Tricks
Tutorial Requests
  • sdjensen's avatar
    sdjensen
    10 years ago

    You could perhaps try something like this - basically I use the same group by, but then in the next step go back to the previous step and then join with the values from the group by then calculated a value for each row that is equal to the max version number per Sales Order and then remove the rows that does not match. You can always add extra steps to remove columns you don't want to keep.

     

     

    CalcMaxVersion = Table.Group(#"NameOfPreviousStep", {"Sales Orders"}, {{"MaxVersion", each List.Max([#"Version number"])}}),
    #"Add Column" = Table.NestedJoin(#"Renamed Columns", "Sales Orders", CalcMaxVersion, "Sales Orders", "MaxVersion", 1),
    #"Expanded MaxVersion" = Table.ExpandTableColumn(#"Add Column", "MaxVersion", {"MaxVersion"}, {"MaxVersion.MaxVersion"}),
    #"Added Conditional Column" = Table.AddColumn(#"Expanded MaxVersion", "RowsToKeep", 
        each if [Version number] = [MaxVersion.MaxVersion] then "Keep" 
            else if [Version number] <> [MaxVersion.MaxVersion] then "Discard" 
            else null ),
    #"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each ([RowsToKeep] = "Keep"))
in
    #"Filtered Rows"
sdjensen's avatar
sdjensen
Solution Sage
10 years ago

ImkeF  - Could you please elaborate how the following steps are obsolete?

 

This is the result from my origional code where I only have 3 rows left after filtering the calculated column "RowsToKeep"

 

If I change the code and stop the script withe "Add Column" as the last step using your code we get this result, so now we have 6 rows and not just the rows with max Version Number per Sales Order.

 

I agree that instead of Table.NestedJoin it might be better to use this code

#"Add Column" = Table.Join(#"Renamed Columns", "Sales Orders", CalcMaxVersion, "Sales Orders", JoinKind.Inner)

 

 - this will make the Table.ExpandTableColumn obsolute, but there is still a need for filtering the table so only the 3 rows with the max Version Number is the end result.

 

Result with Join instead of NestedJoin:

ImkeF's avatar
ImkeF
Community Champion
10 years ago

Sorry, didn't see that there was a field missing on which to combine:

 

#"Add Column" = Table.Join(#"Renamed Columns", {"Sales Orders", "Version number"}, CalcMaxVersion, {"Sales Orders", "MaxVersion"}, JoinKind.Inner)
  • sdjensen's avatar
    sdjensen
    Solution Sage
    10 years ago

    ImkeF - I just had a go trying to figure out how to join in 2 fields and I ended with the same result as you and now the rest of the steps are obsolete.

     

    CalcMaxVersion = Table.Group(#"NameOfPreviousStep", {"Sales Orders"}, {{"MaxVersion", each List.Max([#"Version number"])}}),
    #"Add Column" = Table.Join(#"Renamed Columns", {"Sales Orders", "Version number"}, CalcMaxVersion, {"Sales Orders", "MaxVersion"}, JoinKind.Inner)
in
    #"Add Column"
    • gjh100's avatar
      gjh100
      New Member
      8 years ago

      Hi,

      I have the same situation as the original poster but I keep having syntax issues.  Please would you help?

       

       JobRef    ShippingManifest_Id
      79235       125                               Discard
      79235       113                              Discard
      79235        206                             Display
      80123        252                             Discard
      80123        351                             Display
      90512        231                            Display
      98512        112                            Discard
      99111        111                            Do nothing

    • gjh100's avatar
      gjh100
      New Member
      8 years ago

      Hi,

      I have the same situation as the original poster but I keep having syntax issues.  Please would you help?

       

       JobRef    ShippingManifest_Id
      79235       125                               Discard
      79235       113                              Discard
      79235        206                             Display
      80123        252                             Discard
      80123        351                             Display
      90512        231                            Display
      98512        112                            Discard
      99111        111                            Do nothing

      • ImkeF's avatar
        ImkeF
        Community Champion
        8 years ago

        Hi,

        please check how this works for you:

         

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrc0MjZV0lEyNAKRLpnFyYlFKUqxOkgyhsY4ZIwMzCAyBTmJlWAZCwNDI5BqI1MjND0wGWNTQzQ9lgamhiDVRsa4ZAwN0U2ztDQ0NATLgPXkK+Tll2Rk5qUrxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [JobRef = _t, Shipping = _t, Manifest_Id = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"JobRef", Int64.Type}, {"Shipping", Int64.Type}, {"Manifest_Id", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"JobRef"}, {{"Shipping", each List.Max([Shipping]), type number}})
in
    #"Grouped Rows"

        Imke Feldmann

        www.TheBIccountant.com -- How to integrate M-code into your solution  -- Check out more PBI- learning resources here