Data Duplicacy when Splitting Column by Delimited

[Anonymous]

I have a data set that looks like the following:

CUSTOMER #      LEAD SOURCE          SALES         PROFIT

1234                     CATALOG; VIDEO      $1000        $250

I want to be able to capture that this customer had two originating lead sources. I split the column by the semicolon delimiter into ROWS in Query Editing and get the following data duplicacy when I try to analyze sales and profit:

CUSTOMER #      LEAD SOURCE          SALES         PROFIT

1234                     CATALOG                $1000        $250

1234                      VIDEO                     $1000          $250

In reality, I want to be able to capture that this customer came from two originating lead sources but had total  cumulative sales of $1000 and profit of $250. Does anyone have any idea how to do this correctly in Power BI?

[v-piga-msft]

Hi @Anonymous ,

Have you solved your problem with the suggestion of jthomson ?

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, what is your desired output?

---

In reality, I want to be able to capture that this customer came from two originating lead sources but had total  cumulative sales of $1000 and profit of $250. 

---

I have a little confused about your expected output based on your description.

Best  Regards,

Cherry

[jthomson · Accepted solution]

Probably a few ways to do this - one way would be to count the number of delimiters you have in the lead source column, add one to it to get the number of rows you'd end up having, then making new columns dividing the sales/profit columns by that figure, what you're doing now should then work