none
Previous Month Multiple Colums

    Question

  • Is there a simple way to go about comparing previous month values from column vs. current month values from a different column? For instance I have 5 columns: "Month", "Location", "Customer", "Nominations", "Deliveries". I'd like to compare previous month deliveries to current month nominations.

    Thanks for all the help!

    Tuesday, December 4, 2018 2:33 PM

All replies

  • Option to Merge query on Index from 0 and Index from 1

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,
            {{"Month", Int64.Type}, {"Location", type any}, {"Customer", type any},
            {"Nominations", Int64.Type}, {"Deliveries", Int64.Type}}),
        #"Sorted Rows1" = Table.Sort(#"Changed Type",{{"Month", Order.Ascending}}),
        #"Added Index" = Table.AddIndexColumn(#"Sorted Rows1", "Index", 0, 1),
        #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1),
        #"Merged Queries" = Table.NestedJoin(#"Added Index1", {"Index"},
                                             #"Added Index1", {"Index.1"},
                                             "MergeOnIndexes", JoinKind.LeftOuter),
        #"Removed Other Columns1" = Table.SelectColumns(#"Merged Queries",
            {"Month", "Location", "Customer", "Nominations", "MergeOnIndexes"}),
        #"Expanded Added Index1" = Table.ExpandTableColumn(#"Removed Other Columns1", "MergeOnIndexes",
            {"Deliveries"}, {"PrevMonth.Deliveries"})
    in
        #"Expanded Added Index1"

    Thursday, December 6, 2018 11:58 PM
  • Other option adapted from Colin's reply to Another join problem based on time range

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", Int64.Type}, {"Location", type any},
            {"Customer", type any}, {"Nominations", Int64.Type}, {"Deliveries", Int64.Type}}),   
        AddedColumn = Table.AddColumn(#"Changed Type", "ShiftedTable",
            (i) => Table.SelectRows(#"Changed Type",
                (j) => i[Month]-1 = j[Month]
            ), type table
        ),
        #"Removed Other Columns" = Table.SelectColumns(AddedColumn,
            {"Month", "Location", "Customer", "Nominations", "ShiftedTable"}),
        #"Expanded PreviousMonth" = Table.ExpandTableColumn(#"Removed Other Columns", "ShiftedTable",
            {"Deliveries"}, {"PrevMonth.Deliveries"}),
        #"Changed Type1" = Table.TransformColumnTypes(#"Expanded PreviousMonth",{{"PrevMonth.Deliveries", Int64.Type}})
    in
        #"Changed Type1"

    Friday, December 7, 2018 7:56 AM