none
Insert Rows based in criteria/conditions RRS feed

  • Question

  • 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 year



    Is that possible to transform the original table as mentioneb above through PowerQuery?

    Any help it would be very appreciated

    Best Regards
    Saturday, October 7, 2017 5:20 PM

Answers

  • Try the following (original data coming from an Excel table)

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        ChangedType = Table.TransformColumnTypes(Source,{{"Date", type date}, {"CustomerName", type text}, {"NumContract", Int64.Type}, {"DurationContract", type text}, {"ValueContract", Number.Type}, {"Status", type text}, {"TotalPoints", Int64.Type}}),
        AddedTotalYears = Table.AddColumn(ChangedType, "TotalYears", each Number.From(Text.BeforeDelimiter([DurationContract], " ")), Int64.Type),
        AddedCustom = Table.AddColumn(AddedTotalYears, "Custom", each List.Repeat({[Date]}, [TotalYears])),
        ExpandedCustom = Table.ExpandListColumn(AddedCustom, "Custom"),
        RemovedAddedCustom = Table.RemoveColumns(ExpandedCustom,{"Custom"}),
        GroupedRows = Table.Group(RemovedAddedCustom, {"Date", "CustomerName"}, {{"Table", each _, type table}}),
        AddedCurrentYear = Table.TransformColumns(GroupedRows, {"Table", each Table.AddIndexColumn(_, "CurrentYear", 1, 1)}),
        CombinedTables = Table.Combine(AddedCurrentYear[Table]),
        AddedYearValue = Table.AddColumn(CombinedTables, "YearValue", each [ValueContract]/[TotalYears], Number.Type),
        AddedYearPoints = Table.AddColumn(AddedYearValue, "YearPoints", each [TotalPoints]/[TotalYears], Number.Type),
        AddedStartDate = Table.AddColumn(AddedYearPoints, "StartDate", each Date.AddYears([Date], [CurrentYear] - 1), Date.Type),
        AddedEndDate = Table.AddColumn(AddedStartDate, "EndDate", each Date.AddYears([StartDate], 1) - #duration(1, 0, 0, 0), Date.Type)
    in
        AddedEndDate


    • Edited by Colin Banfield Saturday, October 7, 2017 9:46 PM
    • Marked as answer by ricafonyat Sunday, October 8, 2017 7:42 PM
    Saturday, October 7, 2017 9:45 PM