Help splitting column and counting events

[chris886]

I'm hoping this is easy, I just can't seem to figure it out.

My data source often has multiple entries allowed for a field, which it exports in one column delimited by a semi colon.  These are all text entries and I just need to count them.  I know how to split a column by delimiter, but don't know what to do from here.

If it's a list of names for example, I still want to know how many rows a certain name shows up.  How can I transform the data so my report still counts these names accurately, without effecting all of the other data columns?

Thanks in advance!

[chris886]

Essentially I want to be able to count the 'Name(s)' column, and how many times each name shows up there, without screwing up counts of any other column.

[v-easonf-msft · Accepted solution]

Hi , @chris886 

You can try to create a seperate table for these entries(duplicate original table ->remove other columns->split the column by delimiter->pivot columns). 

Then after  building a  one-to-many relationship with the 'Fact table', you can accurately count these entries based on this new table without affecting all  data columns in the original fact table.

Please check my sample file for more details.

Best Regards,
Community Support Team _ Eason

[richbenmintz]

are you looking to split the column values into rows?

[chris886]

I'm trying to figure out a way to count these events without duplicating the rest of my data.  If I split into rows, then the rest of the data is all duplicated and counting those columns will be incorrect.  I'm aware of the unpivot feature too, but again it seems to duplicate the rest of the data.

I considered pulling this column out into a new query, but then I'm lost on how I'd connect the relationship back to the rest of the columns.

[richbenmintz]

Hi @chris886 ,

Can you provide some sample data and the expected results?