BulletedList + FormulaMap + Unique HELP


I want the result to look like this:

  • Delrin Black - 2
  • Micarta Black - 1
  • Ultem - 1

But somehow I am not seeing how to do it with the Unique()

This works:

Hi @Support_MMVapors :blush: ,

So, I think this is what you’re looking for:
(See the sample below :blush: )

Table.[Body Material].Unique().FormulaMap(
     " - ", 
     Table.Filter([Body Material] = CurrentValue).Count()

And what it does is :

It creates a list of the unique material from the table (like you did in your last screenshot)

Table.[Body Material].Unique()

This returns a list of text values where each item in the list is stored as CurrentValue and in this case, this list looks like this :

Material 1,Material 2,Material 3

Then, like you did, for each item in the list (FormulaMap()), I ask the formula to Concatenate() :

1 - the CurrentValue (which is each material in the list)
2 - the separator " - "
3 - Table.Filter([Body Material] = CurrentValue).Count()


Table.Filter([Body Material] = CurrentValue).Count()

Takes the table and counts how many [Body Material] ([Body Material] is here CurrentValue.[Body Material]) are equal to CurrentValue (CurrentValue here, is the specific item in the list from Table.[Body Material].Unique()

And as FormulaMap() always returns a list all that’s left is to BulletedList() the result :blush:

Now, I think that instead of using text values to get to that result, maybe you could use a LookUp field :thinking:

If you look at the sample below, I created a table for the Materials and linked that table to another table where I randomly selected a material per row.

In my Materials table I can then count how many times each material has been selected in the table and the formula can then become simply this :

    CurrentValue.Name," - ",CurrentValue.[Materials - Count]

It still works in a similar way than the previous one but by using a lookup to store your various materials, it gets easier to correct if there’s any mistake :blush:


Thank you so much! I was staring at my formula the other day for the longest time and I just did not see it! :kissing_heart:

1 Like

No problem @Support_MMVapors :wink: … I’m very glad to know this helped as …

I know that feeling :laughing: … probably a little bit too well :laughing:


This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.