Forum Discussion
Creating new table from existing data
mrothschild Please follow the below steps:
1. Create a New Table as below (This will generate the sequence numbers from 0 till the maximum number available on the Duration field in source table. In this case it is 12)
Test215Series = VAR _MaxVal = MAX(Test215MultiRowSplit[Duration]) RETURN GENERATESERIES(0,_MaxVal)
2a. Then Create a New Table as below (Which will give a cartesian product of source table and the series table that was created above)
Test215Out = CROSSJOIN(Test215MultiRowSplit,Test215Series)
2b. Now, will remove the unnecessary records that are not required by flagging. Note I've renamed the Value field to Period field as requied. Add a new column as below
RemoveFlag = IF(Test215Out[Period]<=Test215Out[Duration],"Y","N")
2c. Filter only RemoveFlag = "Y" which are our expected records and then add a new column as below.
Month = EDATE(Test215Out[StartDate],Test215Out[Period])
Final Result
mrothschild Please follow the below steps:
1. Create a New Table as below (This will generate the sequence numbers from 0 till the maximum number available on the Duration field in source table. In this case it is 12)
Test215Series = VAR _MaxVal = MAX(Test215MultiRowSplit[Duration]) RETURN GENERATESERIES(0,_MaxVal)
2a. Then Create a New Table as below (Which will give a cartesian product of source table and the series table that was created above)
Test215Out = CROSSJOIN(Test215MultiRowSplit,Test215Series)
2b. Now, will remove the unnecessary records that are not required by flagging. Note I've renamed the Value field to Period field as requied. Add a new column as below
RemoveFlag = IF(Test215Out[Period]<=Test215Out[Duration],"Y","N")
2c. Filter only RemoveFlag = "Y" which are our expected records and then add a new column as below.
Month = EDATE(Test215Out[StartDate],Test215Out[Period])
Final Result
As with most things, there are generally more than one solution. I went the Power Query route:
Final Table:
You can step through the applied steps, but a quick rundown:
- Create a list for each Asset ID of all the dates between start and end
- Expand that list, and transform all those dates into EndOfMonth
- Select AssetID and the new date column and remove duplicates
- Group the rows by Asset ID
- Add a new column that will produce a new table, but witih an index ( called period )
- Then just some cleaning up (setting data types and such)
PBIX file if you feel inclined:
https://1drv.ms/f/s!Amqd8ArUSwDSzzEI55iEKiX7TfEC
-Nick