Forum Discussion

ROBINMECH's avatar
ROBINMECH
New Member
1 year ago
Solved

Remove Duplicate data and keep the rows as per latest revision

Dear Members,   I have a power query table on which data values are duplicate on each row except the revision. I want a solution only to keep the rows as per latest revision of the line number. See...
Need Help
  • SundarRaj's avatar
    SundarRaj
    1 year ago

    Hi ROBINMECH ,

    let
    Source = #table(
    {
    "LINE_ID",
    "REV_NO",
    "ALL DATA.PROJECT",
    "ALL DATA.DRAWING_ID",
    "ALL DATA.SPOOL_NO",
    "ALL DATA.EPIC_SPOOL_NO"
    },
    {
    {"007701-MM5177-001-BA-14", "02", "6201414", "4422-SP-XH-DL-007701-MM5177-001-BA-14-02", "4422-SP-XH-DL-007701-MM5177-001-BA-14-SP06", "01414-42-007533"},
    {"007701-MM5177-001-BA-14", "02", "6201414", "4422-SP-XH-DL-007701-MM5177-001-BA-14-02", "4422-SP-XH-DL-007701-MM5177-001-BA-14-ER", "01414-42-001172"},
    {"007701-MM5177-001-BA-14", "02", "6201414", "4422-SP-XH-DL-007701-MM5177-001-BA-14-02", "4422-SP-XH-DL-007701-MM5177-001-BA-14-SP01", "01414-42-001173"},
    {"007701-MM5177-001-BA-14", "02", "6201414", "4422-SP-XH-DL-007701-MM5177-001-BA-14-02", "4422-SP-XH-DL-007701-MM5177-001-BA-14-SP02", "01414-42-001173"},
    {"007701-MM5177-001-BA-14", "02", "6201414", "4422-SP-XH-DL-007701-MM5177-001-BA-14-02", "4422-SP-XH-DL-007701-MM5177-001-BA-14-SP03", "01414-42-001174"},
    {"007701-MM5177-001-BA-14", "02", "6201414", "4422-SP-XH-DL-007701-MM5177-001-BA-14-02", "4422-SP-XH-DL-007701-MM5177-001-BA-14-SP08", "01414-42-007535"},
    {"007701-MM5177-001-BA-14", "02", "6201414", "4422-SP-XH-DL-007701-MM5177-001-BA-14-02", "4422-SP-XH-DL-007701-MM5177-001-BA-14-SP07", "01414-42-007534"},
    {"007701-MM5177-001-BA-14", "02", "6201414", "4422-SP-XH-DL-007701-MM5177-001-BA-14-02", "4422-SP-XH-DL-007701-MM5177-001-BA-14-SP04", "01414-42-001175"},
    {"007701-MM5177-001-BA-14", "02", "6201414", "4422-SP-XH-DL-007701-MM5177-001-BA-14-02", "4422-SP-XH-DL-007701-MM5177-001-BA-14-SP05", "01414-42-001176"},
    {"007701-MM5177-001-BA-14", "02", "6201414", "4422-SP-XH-DL-007701-MM5177-001-BA-14-02", "4422-SP-XH-DL-007701-MM5177-001-BA-14-SP03", "01414-42-001174"},
    {"007701-MM5177-001-BA-14", "01", "6201414", "4422-SP-XH-DL-007701-MM5177-001-BA-14-01", "4422-SP-XH-DL-007701-MM5177-001-BA-14-SP04", "01414-42-001175"},
    {"007701-MM5177-001-BA-14", "01", "6201414", "4422-SP-XH-DL-007701-MM5177-001-BA-14-01", "4422-SP-XH-DL-007701-MM5177-001-BA-14-SP05", "01414-42-001176"},
    {"007701-MM5177-001-BA-14", "01", "6201414", "4422-SP-XH-DL-007701-MM5177-001-BA-14-01", "4422-SP-XH-DL-007701-MM5177-001-BA-14-SP06", "01414-42-007533"},
    {"007701-MM5177-001-BA-14", "01", "6201414", "4422-SP-XH-DL-007701-MM5177-001-BA-14-01", "4422-SP-XH-DL-007701-MM5177-001-BA-14-SP07", "01414-42-007534"},
    {"007701-MM5177-001-BA-14", "01", "6201414", "4422-SP-XH-DL-007701-MM5177-001-BA-14-01", "4422-SP-XH-DL-007701-MM5177-001-BA-14-SP08", "01414-42-007535"},
    {"007701-MM5177-001-BA-14", "01", "6201414", "4422-SP-XH-DL-007701-MM5177-001-BA-14-01", "4422-SP-XH-DL-007701-MM5177-001-BA-14-ER", "01414-42-001172"},
    {"007701-MM5177-001-BA-14", "01", "6201414", "4422-SP-XH-DL-007701-MM5177-001-BA-14-01", "4422-SP-XH-DL-007701-MM5177-001-BA-14-SP02", "01414-42-001173"},
    {"007701-MM5177-001-BA-14", "01", "6201414", "4422-SP-XH-DL-007701-MM5177-001-BA-14-01", "4422-SP-XH-DL-007701-MM5177-001-BA-14-SP08", "01414-42-007535"},
    {"007701-MM5177-001-BA-14", "01", "6201414", "4422-SP-XH-DL-007701-MM5177-001-BA-14-01", "4422-SP-XH-DL-007701-MM5177-001-BA-14-SP07", "01414-42-007534"},
    {"007701-MM5177-001-BA-14", "01", "6201414", "4422-SP-XH-DL-007701-MM5177-001-BA-14-01", "4422-SP-XH-DL-007701-MM5177-001-BA-14-SP06", "01414-42-007533"},
    {"007701-MM5177-001-BA-14", "00", "6201414", "4422-SP-XH-DL-007701-MM5177-001-BA-14-00", "4422-SP-XH-DL-007701-MM5177-001-BA-14-SP05", "01414-42-001176"},
    {"007701-MM5177-001-BA-14", "00", "6201414", "4422-SP-XH-DL-007701-MM5177-001-BA-14-00", "4422-SP-XH-DL-007701-MM5177-001-BA-14-SP04", "01414-42-001175"},
    {"007701-MM5177-001-BA-14", "00", "6201414", "4422-SP-XH-DL-007701-MM5177-001-BA-14-00", "4422-SP-XH-DL-007701-MM5177-001-BA-14-SP02", "01414-42-001173"},
    {"007701-MM5177-001-BA-14", "00", "6201414", "4422-SP-XH-DL-007701-MM5177-001-BA-14-00", "4422-SP-XH-DL-007701-MM5177-001-BA-14-SP01", "01414-42-001172"},
    {"007701-MM5177-001-BA-14", "00", "6201414", "4422-SP-XH-DL-007701-MM5177-001-BA-14-00", "4422-SP-XH-DL-007701-MM5177-001-BA-14-ER", "01414-42-001172"},
    {"007701-MM5177-001-BA-14", "00", "6201414", "4422-SP-XH-DL-007701-MM5177-001-BA-14-00", "4422-SP-XH-DL-007701-MM5177-001-BA-14-SP03", "01414-42-001174"}
    }
    ),
    #"Grouped Rows" = Table.Group(Source, {"ALL DATA.EPIC_SPOOL_NO"}, {{"All", each _, type table [LINE_ID=text, REV_NO=text, ALL DATA.PROJECT=text, ALL DATA.DRAWING_ID=text, ALL DATA.SPOOL_NO=text, ALL DATA.EPIC_SPOOL_NO=text]}}),
    Tables = Table.TransformColumns ( #"Grouped Rows" , { "All" , each Table.FirstN ( _ , 1 ) } )[[All]],
    Table = Table.ExpandTableColumn(Tables, "All", {"LINE_ID", "REV_NO", "ALL DATA.PROJECT", "ALL DATA.DRAWING_ID", "ALL DATA.SPOOL_NO", "ALL DATA.EPIC_SPOOL_NO"}, {"LINE_ID", "REV_NO", "ALL DATA.PROJECT", "ALL DATA.DRAWING_ID", "ALL DATA.SPOOL_NO", "ALL DATA.EPIC_SPOOL_NO"})
    in
    Table

     

     

slorin's avatar
slorin
Icon for Super User rankSuper User
1 year ago

When you group you can choose: sum, min, max... but not "first"
So you choose Sum but then you change the code

= Table.Group(Your_Source, {a list of columns}, {{"New column", each List.SumFirst([YourColumn]), type text}})

 Stéphane

  • v-aatheeque's avatar
    v-aatheeque
    Icon for Community Support rankCommunity Support
    1 year ago

    Hi ROBINMECH ,


    We noticed that a solution provided by SundarRaj to your query regarding retaining only the latest revision per line number in your Power Query table. If the solution helped resolve your issue, Marking it as Accepted Answer so others with similar challenges can benefit from it.

     

    If you’re still facing issues or need further clarification, please feel free to drop a comment under the thread we’ll be happy to assist further.

    Thank you for your contribution in enhancing Microsoft Fabric Community Forum.