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

     

     

SundarRaj's avatar
SundarRaj
Icon for Super User rankSuper User
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

 

 

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

Hi ROBINMECH ,


Has your issue been resolved? If a community member's response addressed your query, please consider marking it as Accepted Answer and click Yes if you found it helpful.

If you have any further questions, feel free to reach out.
Thank you for being a valued member of the Microsoft Fabric Community Forum!

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

    Hi ROBINMECH ,

    If the information addressed by SundarRaj  slorin  is helpful, please accept the answer by clicking the "Upvote" and "Accept Answer" on the post. If you are still facing any issue, please let us know in the comments. We are glad to help you.

     

    We value your feedback, and it will help us to assist others who might have a similar query. Thank you for your contribution in enhancing Microsoft Fabric Community Forum.