Forum Discussion

JenaT's avatar
JenaT
Frequent Visitor
8 years ago
Solved

Split a cell into multiple rows and calculating values

I have invoice data (product names and purchased pieces aggregated on 1 row) that I need to transform into sales data, i.e. disaggregate the product name from the nr of pieces and also split the sale...
Need Help
  • MFelix's avatar
    MFelix
    8 years ago

    Hi JenaT,

     

    The question is how you make that split is the unit price always the same for the same invoice?

     

    You have several quantities but only one line for total value, what is the rule to get the split per product?

     

    Regards,

    MFelix

MFelix's avatar
MFelix
Super User
8 years ago

JenaT,

 

Is your information with a line break a you present in you sample data?

 

If so you have to first split the cell by row by the line break simbol "#(lf)" and then make the split by columns by the "-".

 

Be aware that taking into account the sales columns you present the calculation of the Sales will bee repeat in all of your columns so the second row you present will have:

Jill | 13/07/2018 | Product 1 - 1 | 50

Jill | 13/07/2018 | Product 2 - 4 | 50

 

See below the screenshots and attach a PBIX file.

 

 

Regards,

MFelix

 

 

 

 

JenaT's avatar
JenaT
Frequent Visitor
8 years ago

 

Thanks, I did not know about the line feed.

But I am still presented with the problem of splitting sales amounts between the products. Is there any possible solution to that using the advanced editor for example or any other way?

  • MFelix's avatar
    MFelix
    Super User
    8 years ago

    Hi JenaT,

     

    The question is how you make that split is the unit price always the same for the same invoice?

     

    You have several quantities but only one line for total value, what is the rule to get the split per product?

     

    Regards,

    MFelix