locked
Adding rows RRS feed

  • Question

  • Hello,

    I have the M Query below that I need to modify somewhat. In the 7th row loco_role is set to Inactive. The modification is to set loco_role to Inactive if the value in column A is not the same for row x and y, and if it is the same set loco_role to Active instead.

    I haven't managed to solve this on my own and would really appreciate help.

    //Caj

    let
        Inactivity = let
        Source = XXXXXXX,
         CombineTwoRows = (x, y) =>
    let
                 combine = x[loco_code] = y[loco_code] and x[End] < y[Start],
                 added = Record.Combine({x, [Start=x[End], End=y[Start], loco_role="Inactive"]}),
                 result = if combine then {added, y} else {y}
             in result,
         GenerateStandingRows = (table, combine) =>
             let
                 Handle = (x, y) => {x, y},
                 buffered = Table.Buffer(table),
                 n = Table.RowCount(buffered),
                 windows = List.Generate(
                     () => {1, {buffered{0}}},
                     (x) => x{0} <= n,
                     (x) => {x{0} + 1, if x{0} < n then combine(buffered{x{0}-1}, buffered{x{0}}) else {buffered{x{0}}}},
                     (x) => x{1})
             in
                 windows,
         InsertInactivity = (table) => Table.FromRecords(List.Combine(GenerateStandingRows(table, CombineTwoRows))),
         TestData = Source
     in
         InsertInactivity(TestData)
    in
        Inactivity


    Csten

    Friday, October 24, 2014 1:37 PM

Answers

  • Perhaps

    added = Record.Combine({x, [Start=x[End], End=y[Start], loco_role=if x[A]=y[A] then "Active" else "Inactive"]}),

    • Marked as answer by csten Friday, October 24, 2014 2:53 PM
    Friday, October 24, 2014 2:25 PM

All replies

  • Perhaps

    added = Record.Combine({x, [Start=x[End], End=y[Start], loco_role=if x[A]=y[A] then "Active" else "Inactive"]}),

    • Marked as answer by csten Friday, October 24, 2014 2:53 PM
    Friday, October 24, 2014 2:25 PM
  • Thanks!

    Csten

    Friday, October 24, 2014 2:53 PM