How to convert a table to one that is easier to work with

[Mister_PowerBI]

Hello everybody, 

I've been trying to find a solution for this problem for a while now, and I am not able to figure it out. I am hoping there are some Power BI Experts that can help me out with this one.

I have a table like below containing invoicing rules like this (with sample data): 

Customer	Item	Price	From	Till (made with DAX)
John	Spotify	€ 10	02-2022	05-2022
John	Netflix	€ 8	06-2022
John	Spotify	€ 12	05-2022
Thomas	Spotify	€ 10	02-2022
Thomas	Netflix	€ 8	05-2022	06-2022
Thomas	Netflix	€ 15	06-2022

Because I find it very hard to analyse this date (especially because it is combined with other tables), I would like to convert this to a table like below for the coming two years: 

Customer	Item	Price	Month
John	Spotify	€ 10	02-2022
John	Spotify	€ 10	03-2022
John	Spotify	€ 10	04-2022
John	Netflix	€ 8	06-2022
John	Spotify	€ 12	05-2022
John	Spotify	€ 12	06-2022
Thomas	Spotify	€ 10	02-2022
John	Spotify	€ 12	03-2022
John	Spotify	€ 12	04-2022
John	Spotify	€ 12	05-2022
John	Spotify	€ 12	06-2022
Thomas	Netflix	€ 8	05-2022
Thomas	Netflix	€ 15	06-2022

Based on this second table, it is way easier to combine the price for a specific month with the amount of users and some other data that affects the overall income. But I can't find a way to create table 2 based on the data in table 1.

Does anybody have any idea on how to add a table 2 to the datamodel?

[v-yueyunzh-msft · Accepted solution]

Hi , @Mister_PowerBI 

Based on the data you provided, it's hard for me to see what rules table2 is based on.

And the [Till] i think it is created by the other table by dax like a calculated column .

The first line is from February to February, but below there is only February to April. Is there a deviation in my understanding of your needs?

Second, if you want to generate a new table with start and end dates, you can use the GenerateSeries or calendar functions to generate a date table for each row, and then use crossjoin() to merge them.

If this method does not meet your needs, you can provide us with your special sample data and the right desired output sample data in the form of tables, so that we can better help you solve the problem.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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