Transform data from a summarized format into a more granular format

[CAOLU10]

I would like to know if Power BI has such function that for example. I have a regular allowance payment to employee and the data report is only show one row with the payment period and i would to like convert to monthly payment items based on the start date and end date? can Power BI make it happen?

[Ashish_Mathur]

Hi,

To create one row for each month which falls between the start and end date, follow the steps shown in this Blog article - Generating Rows by Month for Date Ranges in Power Query | by Daniel Marsh-Patrick | Daniel Marsh-Pat...

[johnt75 · Accepted solution]

You could create a new table like

Detailed Table = SELECTCOLUMNS(
	GENERATE(
		'Table',
		VAR _Dates = FILTER( CALENDAR( 'Table'[Start Date], 'Table'[End Date] ), DAY( [Date] ) = 1 )
		VAR Result = SELECTCOLUMNS(
			_Dates,
			"Start Month", [Date],
			"End Month", EOMONTH( [Date] , 0 )
		)
		RETURN Result
	),
	[Name],
	[Allowance],
	[Start Month],
	[End Month]
)

[CAOLU10]

Thank you soooooo much for the solution which solve my problem and give me more insights for the new function with VAR😊