BulletedList + FormulaMap + Unique HELP

[Support_MMVapors]

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:

[Pch]

Hi @Support_MMVapors ,

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

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

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()

where

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

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

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 :

Materials.FormulaMap(
  Concatenate(
    CurrentValue.Name," - ",CurrentValue.[Materials - Count]
  )
).BulletedList()

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 …

[Support_MMVapors]

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

[Pch]

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

@Support_MMVapors

I was staring at my formula the other day for the longest time and I just did not see it!

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