powerquery

How to pick the next month's value?

Want to know how to select the row value based on month header using power query? I have few columns in excel dataset which is loaded and transformed in Power Query. In the below table the months keep changing and new months will be getting added. I have below columns :

ID Name Category Dec Jan Feb
001 per 1 active 100 100 100
002 per 2 inactive 100 100 100
003 per 3 inactive 0 0 0

I want to filter out persons with 'inactive' category and who are having 100 in next month(Current month +1). In the above it should pick person with 002 ID as he is 'inactive' and still has 100 in Jan month. please help as it looks easy but not able to get the right logic to use.

Solution

  • here is a workaround for you.

    I will create a month table.

    enter image description here

    1. select the first three columns and unpivot other tables enter image description here

    2. merge two tables and bring monthnum to the main table

    enter image description here

    1. add a new column to show next month's num

      if Date.Month(DateTime.LocalNow()) = 12 then 1 else +1

    enter image description here

    1. add a new column to mark the rows that we need

      =if [Next Month]=[monthnum] and [Category]="inactive" and [Value] <>0 then "y" else "n"

    enter image description here

    1. filter and select the value is "y" and delete created columns

    enter image description here

    below is the completed M coding

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjAwVNJRKkgtUgDRicklmWWpQIahgQEKGasDUmoEVQqiM/MIKDaGKjZGVWwAx7GxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Name = _t, Category = _t, Dec = _t, Jan = _t, Feb = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Name", type text}, {"Category", type text}, {"Dec", Int64.Type}, {"Jan", Int64.Type}, {"Feb", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ID", "Name", "Category"}, "Attribute", "Value"),
    #"Merged Queries" = Table.NestedJoin(#"Unpivoted Other Columns", {"Attribute"}, month, {"month"}, "month", JoinKind.LeftOuter),
    #"Expanded month" = Table.ExpandTableColumn(#"Merged Queries", "month", {"monthnum"}, {"monthnum"}),
    #"Added Custom" = Table.AddColumn(#"Expanded month", "Next Month", each if Date.Month(DateTime.LocalNow()) = 12 then 1 else +1),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each if [Next Month]=[monthnum] and [Category]="inactive" and [Value] <>0  then "y" else "n"),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Custom] = "y")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"monthnum", "Next Month", "Custom"})
in
    #"Removed Columns"