Hello!
I would like to know if it is possible through PowerQuery to insert rows in a table based on certain criteria/condition
In my case I have the initial table:
PS.: Here in Brazil our date format is dd.mm.yy
Date CustomerName NumContract DurationContract ValueContract Status TotalPoints 9/7/14 Carlos 110 3 anos 9000 Ativo 30.000 15/7/14 Ricardo 111 3 anos 12000 Cancelado 60.000 My goal is to achieve the following table:
Date CustomerName NumContract DurationContract ValueContract Status TotalPoints TotalYears YearValue YearPoints CurrentYear StartDate EndDate 9/7/14 Carlos 110 3 anos 9000 Ativo 30.000 3 3.000 10.000 1 9/7/14 8/7/15 9/7/14 Carlos 110 3 anos 9000 Ativo 30.000 3 3.000 10.000 2 9/7/15 8/7/16 9/7/14 Carlos 110 3 anos 9000 Ativo 30.000 3 3.000 10.000 3 9/7/16 8/7/17 15/7/14 Ricardo 111 3 anos 12000 Cancelado 60.000 3 4.000 20.000 1 15/7/14 14/7/15 15/7/14 Ricardo 111 3 anos 12000 Cancelado 60.000 3 4.000 20.000 2 15/7/15 14/7/16 15/7/14 Ricardo 111 3 anos 12000 Cancelado 60.000 3 4.000 20.000 3 15/8/15 14/7/17 The first step is to insert rows: If PrazoContrato = 3 anos (3 Years) so we have to have 3 rows for that Contract
The second step is about to insert new (6) columns on the right based in certain conditions
- TotalYears = > Same as DurationContract but as value not
- YearValue = > is equal ValueContract divided TotalYears
- YearPoints = > is equal TotalPoints divided TotalYears
- CurrentYear = > here in each row of the contract we set the current year for that contract
- StartDate = > here in each row of the contract we set the start date of the current year
- EndDate = > here in each row of the contract we set the end date of the current yearIs that possible to transform the original table as mentioneb above through PowerQuery?
Any help it would be very appreciated
Best Regards
Hi Jose,
Thanks for sharing a sample file. In the attached I've inserted the rows for you. Please inspect the 'Applied Steps' to see how it was done as you will no doubt want to add more conditional columns to allow for contracts that span more than 4 years.
In regards to the other columns, I think adding these should be fairly straight forward for you now, but if you have any troubles please come back.
Mynda
There is a nice trick I use to create new rows, which may be very useful in case your data does not have an equal amount of rows to be added. This way you can add a variable amount of rows to each existing rows.
You can simply add a column with this formula:
= Table.AddColumn(Source, "Custom", each Text.Repeat("a,",[Year]-1)&"a")
Where the Year column holds the number of rows needed for each existing row.
All you have to do now is to Split this new column by comma delimiter, the trick is to split into rows, not into columns. This action will also copy all other columns, no need to fill down.
See the attached file for an example.
