Forum Discussion

MarianneElver's avatar
MarianneElver
Helper III
9 years ago
Solved

Adding conditional unique index column based on several criterias

The challenge is the need for having a column listing an existing unique ID or create new unique ID based on the following criteria:   - If the customer no is recognized, then use the existing uniq...
Need Help
  • MarianneElver's avatar
    MarianneElver
    9 years ago
    Thank you so much, looks very useful. I will try to implement this and get back with feedback as soon as possible. Thanks again!
ImkeF's avatar
ImkeF
Community Champion
9 years ago

Hi Marianne,

if my understanding of  your request is correct, then PowerBI isn't the right tool for it:

You're importing data in Power BI and want to create a value that sticks to the imported data, also when the next refresh is done?

Problem is, that there is currently no concept for storing such data in PBI. So you would need to export your new table with the newly generated ID's in order for them to be kept ... & reimported & merged with the new data (2nd run), where again new rows with empty ID's will come in.

MarianneElver's avatar
MarianneElver
Helper III
9 years ago
Hi Imke, Thanks for your reply. I see, would data also not be stored if the generated IDs were fed/added into another table? I no, then is there any other method withing PBI for calculating the unique numbers of customers (and use this number in various other calculations) if the criteria for not counting the same customer twice are met? Thanks in advance, Marianne
  • ImkeF's avatar
    ImkeF
    Community Champion
    9 years ago

    Yes, you can create an Index-column like this, that will be recreated completely everytime you refresh your query like this:

     

    let
    Source = Tabelle2,
    #"Grouped Rows" = Table.Group(Source, {"Phone"}, {{"All", each Table.AddIndexColumn(_, "NestedIndex",1,1), type table}}),
    TelIndex = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1),
    HasMatches = Table.AddColumn(TelIndex, "Custom", each List.Count(List.Union({[All][email], [All][Customer Number], [All][Customer name]})) <> List.Count(List.Distinct(List.Union({[All][email], [All][Customer Number], [All][Customer name]})))),
    #"Expanded All" = Table.ExpandTableColumn(HasMatches, "All", {"Customer name", "Customer Number", "email", "NestedIndex"}, {"Customer name", "Customer Number", "email", "NestedIndex"}),
    #"Added Custom" = Table.AddColumn(#"Expanded All", "NewIndex", each if [Custom] = false then Text.From([Index])&"-"& Text.From([NestedIndex]) else [Index]),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"NestedIndex", "Index", "Custom"})
in
    #"Removed Columns1"

     

    Just replace "Tabelle2" in the Source-step by the name of your query/table.

    Question is what shall be returned, if there are more than 2 rows per telephone-number, and only 2 are matching an one is distinct. Using this, code, the non-matching row would also get the same ID. Not sure if this is what you want.

    • MarianneElver's avatar
      MarianneElver
      Helper III
      9 years ago
      Thank you so much, looks very useful. I will try to implement this and get back with feedback as soon as possible. Thanks again!
    • MarianneElver's avatar
      MarianneElver
      Helper III
      9 years ago
      Hi, This IS actually what I am looking for! Will this be "stored forever" in Power BI alongside with the rest of the data? I think I will also change the grouping from group by Phone to Customer Number, to follow the Customer number-email-phone check in that order. Thanks a lot, Marianne
      • ImkeF's avatar
        ImkeF
        Community Champion
        9 years ago

        Great & yes, this will be stored and executed everytime you hit the refresh-button :-)