Forum Discussion

chris886's avatar
chris886
Frequent Visitor
5 years ago
Solved

Help splitting column and counting events

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!

Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    5 years ago

    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

5 Replies

    • chris886's avatar
      chris886
      Frequent Visitor
      5 years ago

      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.

  • chris886's avatar
    chris886
    Frequent Visitor
    5 years ago

    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's avatar
      v-easonf-msft
      Icon for Community Support rankCommunity Support
      5 years ago

      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