Forum Discussion
Split single row of data in Power BI data source into multiple rows
I have a table in a Power BI data source with a column for term start and term end date (term length can be longer than a month), along with meta data on the term. I need to report on status of purchased terms as at the end of each month. As far as I can see, the best way of accomplishing this would be to create a calculated table with an entry for each month on which a term is active at its end. (From this calculated table I can then perform the aggregations as needed)
For example, an entry in the original table with the following data:
TermStartDate TermEndDate PurchaseAmount 2018-01-03 2018-04-12 100
Would end up in the calculated table as follows:
MonthPurchased PurchaseAmount 2018-01 100 2018-02 100 2018-03 100
How to accomplish this? Is there a better way than creating a separate calculated table to get this data? Any help or advise is appreciated!
Hi Anonymous
You could create this table with Power Query
Open Edit Queries
Create a copy of your data table, in this copied table
1.Add a custom column(Add column->custom column)
=List.Dates([termstartdate],Duration.TotalDays([termenddate]-[termstartdate])+1,#duration(1,0,0,0))
Then click on the arrow on the field to "Expand to new rows"
2. select the [listdate] column, then select "Add column"->"Date"->Month->End of Month
You will get a column to show the end of month for each month of [listdate] column
3.create a conditional column(Add column->Add conditional column)
4. then click on the "down" arrow on the [filtercondition] column, select "Remove empty", you will get such table
Finally, you could remove columns from this table except the [purchase_id], [purchase_amount], [filtercondition] columns
Please refer to my pbix
Best reagrds
Maggie
2 Replies
v-juanli-msftCommunity Support
Hi Anonymous
You could create this table with Power Query
Open Edit Queries
Create a copy of your data table, in this copied table
1.Add a custom column(Add column->custom column)
=List.Dates([termstartdate],Duration.TotalDays([termenddate]-[termstartdate])+1,#duration(1,0,0,0))
Then click on the arrow on the field to "Expand to new rows"
2. select the [listdate] column, then select "Add column"->"Date"->Month->End of Month
You will get a column to show the end of month for each month of [listdate] column
3.create a conditional column(Add column->Add conditional column)
4. then click on the "down" arrow on the [filtercondition] column, select "Remove empty", you will get such table
Finally, you could remove columns from this table except the [purchase_id], [purchase_amount], [filtercondition] columns
Please refer to my pbix
Best reagrds
Maggie
Thanks Maggie.
I got his working basically 10 minutes ago :) My approach was very similar to your proposed answer, for reference I detail the steps below:
- Change start and end date column data types from Datetime to Date. <- This is needed to ensure we only generate dates on day boundaries in the next step
- Add custom column with the following formula:
Month = List.Select( List.Dates([TermStartDate], Number.From([TermEndDate] - [TermStartDate]) +1, #duration(1, 0, 0, 0)), each _ = Date.EndOfMonth(_) )
- Expand to new rows on the new Month column
- Use Detect Data Type option on the Month column to change the datatype from Any to Date (for some reason I cannot manually select Date, the DataType menu option is greyed out on the Month column)
- No further filtering needed in my case since all purchases must have both a start and end date