none
Add conditional column for duplicates RRS feed

  • Question

  • Hi Everyone,

    I have a feeling that what i am going to ask here might not be possible at all but i am just here to give it a try in case there is someone out there with exceptional talent. 

    The question is, i want to add a conditional column for duplicates in my query. The code i am looking for would look a bit like the following when its done..

    If(Item ID = 5127730, SKU Description = "HANDUA Keg 30l", "HANDUA 24x33cl","HANDUA Export LN 24x33cl" else if Item ID = 5127690, SKU Description ="TUXIDO Regular Export LN 24x33cl","TUXIDO Regular LN 24x33cl","TUXIDO Regular EURO 20x50cl","TUXIDO Regular Keg 30L","TUXIDO Special XLN 20x50","TUXIDO Regular EURO 20x50cl")

    This means if the system founds Item ID 5127730 many times, i want it to assign the sku description for each duplicate item ID evenly so the final output would look something like the one i have attached here. Now, i know that there is just a really narrow possibility for it to work but just to try...

    Thanks for the help!

    Friday, August 17, 2018 11:15 AM

Answers

  • Hi Johndree,

    if my understanding is right, this is doable. Please paste this code into the advanced editor and follow the steps:

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXLLLCouUfAsSc0FcoK9QxUclWJ18EoZgXipyfl5KchyTkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Item ID" = _t, #"Item description" = _t, #"SKU description" = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item ID", Int64.Type}, {"Item description", type text}, {"SKU description", type text}}),
        #"Grouped Rows" = Table.Group(#"Changed Type", {"Item ID"}, {{"Partition", each _, type table}, {"Count", each Table.RowCount(_), type number}}),
        #"Expanded Partition" = Table.ExpandTableColumn(#"Grouped Rows", "Partition", {"Item description", "SKU description"}, {"Item description", "SKU description"}),
        #"Added Custom" = Table.AddColumn(#"Expanded Partition", "Consolidated Field", each if [Count] >0 then [SKU description] else [Item description])
    in
        #"Added Custom"

    It groups on the Item ID to retrieve the number of rows with the same ID (Count) while mainting its original content (Partition):

    Then you expand that column and add a custom column which checks if the row is a dup (Count >0)  -> then does sth -> if not -> do sth different:


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Saturday, August 18, 2018 10:03 AM
    Moderator