Count several factors of variable without duplicating ID

[cibgks]

Hello everyone,

I am facing the following problem for a couple of weeks and I couldn't find a solution yet. Any help will be appreciated.

To clarify the problem, suppose I have this table:

ID                       |           SOURCE

1                        |                s1

2                        |                s2

3                        |              s1,s2

I want to show this result in the dashboard

SOURCE            |            COUNT

s1                      |                2

s2                      |                2

without duplicating the third row of my dataframe.

How can I do that?

Thanks in advance

[v-yuezhe-msft]

@cibgks,

The method that I can think of is to split columns in your table and then create a table visual as shown in the following screenshot.



Regards,
Lydia

[cibgks]

Hi Lydia, thanks for your time and answer.

That solution could work, but I should not be duplicating IDs because other variables would be duplicated (for example Amount).

[cibgks]

I found a way to solve the problem, but it is taking too much time to refresh in a small dataset (60k rows). Any ideas how to optimize this M code?

#"test" = Table.AddColumn(#"changed", "counter", (countFunction) =>
List.Sum(Table.TransformRows(#"previoustable", each if Text.Contains(_[source1], countFunction[Source])  then 1 else 0 )))