How to duplicate and amend rows based on column values

[JonasP]

Hello community,

I have a dataset with revenue data from invoices where the type of revenue (e.g. service revenue, retail revenue, etc.) is defined by the expression in certain colums. I am looking for a way to split these invoices into multiple rows, depending of the values of the revenue type columns.

Here is an example for the dataset and the desired outcome:

A solution for this would be so immensely helpful, so thank you very much in advance to anyone interested in looking into this!

[Mikelytics · Accepted solution]

Hi Jonas,

There are many answers to your request. FIrst of all is that you can calculate the total using Measures afterwards or use the total functionality in the report view.

Having other columns there which should not be duplciates you should consider splitting the tables into multiple tables because otherwise you have high amount of duplication or you have low amount of flexibility.  However from my understanding you should think about how your starschema model will look like. Because what you ask is not following star schema best practices.

I hope it helps you a little bit but it seems that there is a bigger picture which has to be clarified in your case.

Best regards

Michael

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your kudos.

[JonasP]

Hi Mike,
thank you very much for further elaboration.

My core problem is that the Total Net column has valid values, whereas the other columns are prone to errors as these are currently manually entered by my colleagues. That's why I cannot simply sum these values up to get correct Total Net.

But your answer indeed helped me to better understand the problem and the intricacies regarding the star scheme. I will now pursue fixing the problem in our datasource / process rather than in PowerBI.

Thank you very much!

[Mikelytics]

Hi JonasP,

In cases like this you would typically unpivpt the data to use it more flexible for any scenario. Can you please check whether this also brings you to your target?

Base Table

Delete Total Column

click on Invoice Nr column and use the "unpivot other columnns" feature

result

after you load the table you can do all kinds of stuff with the table since it is unpivoted.

Best regards

Michael

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your kudos.

[JonasP]

Hi Michael,

first of all thank you for the quick and elaborate response.
This could be the way to do it, but I still have some limitations for my use case:

1. I will still need to retain the total Net Revenue column.
2. My Dataset contains numerous other columns that would have to clone their information to the new rows like the Invoice Nr. field in your example

Would this be possible?