Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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!

1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
Community 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))

12.png

 

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)

13.png

 

4. then click on the "down" arrow on the [filtercondition] column, select "Remove empty", you will get such table

14.png

 

Finally, you could remove columns from this table except the [purchase_id], [purchase_amount],  [filtercondition] columns

15.png

 

 

Please refer to my pbix

 

Best reagrds

Maggie

 

View solution in original post

2 REPLIES 2
v-juanli-msft
Community Support
Community 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))

12.png

 

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)

13.png

 

4. then click on the "down" arrow on the [filtercondition] column, select "Remove empty", you will get such table

14.png

 

Finally, you could remove columns from this table except the [purchase_id], [purchase_amount],  [filtercondition] columns

15.png

 

 

Please refer to my pbix

 

Best reagrds

Maggie

 

Anonymous
Not applicable

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:

 

  1. 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
  2. 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(_) )
  3. Expand to new rows on the new Month column
  4. 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)
  5. No further filtering needed in my case since all purchases must have both a start and end date

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.