Forum Discussion
Transform data from a summarized format into a more granular format
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?
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] )
3 Replies
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] )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-Patrick | Medium
