How can I add a new column that compares the status of a product in the current month to the previou

[kittipunK]

"Hi there,

I have an issue with Power Query, and I would like to create a new column called "status" to check my products based on a condition. If a product appears in the previous month, it should be labeled as "Active." If it doesn't appear, it should be labeled as "New." I've tried several formulas, but I keep getting an error code.

The error message is: 'Expression.Error: A cyclic reference was encountered during the evaluation.' Can anyone help me solve this issue?

Please let me know if you need any further information or clarification."

Thanks in advance

[AlienSx]

Hello, @kittipunK 

let
    Source = your_table,
    f = (lst as list) =>
        [maxx = List.Count(lst),
        out = List.Generate(
            () => [i = 0, r = lst{0} & [status = "New"]],
            (x) => x[i] < maxx,
            (x) => 
                [i = x[i] + 1, 
                r = lst{i} & [status = if Date.AddMonths(Date.StartOfMonth(lst{i}[DATE]), -1) = Date.StartOfMonth(lst{i - 1}[DATE]) then "Active" else "New"]],
                (x) => x[r]
        )][out],
    group = Table.Group(Source, "PRODUCT", {{"all", each f(Table.ToRecords(Table.Sort(_, "DATE")))}})[all],
    z = Table.Sort(Table.FromRecords(List.Combine(group)), "DATE")
in
    z

[kittipunK]

Thank you sir, but I try use this formula still cyclic error. I m not sure how to solved it.

[AlienSx]

---

@kittipunK wrote:

I m not sure how to solved it.

---

show your code, please. 

[kittipunK]

[AlienSx]

@kittipunK you're not suppose to add new column. The code is taking care of that. Now you are trying to add new column inside Table1 table with a reference to itself. This explains cyclic reference error. Please check attached file.  

[kittipunK]

Thank you, but I have an issue with the status. If I record data multiple times within a month, why is the status within the month not the same value, as shown in the picture?

[AlienSx · Accepted solution]

@kittipunK I did not consider this scenario (more than 1 product per month). This is even better - I simplified my code. So open Advanced Editor and replace code with the following: 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DATE", type date}, {"PRODUCT", type text}}),
    f = (tbl as table) =>
        [months = List.Buffer(List.Distinct(List.Transform(tbl[DATE], (x) => Date.StartOfMonth(x)))),
        status = Table.AddColumn(
            tbl, "STATUS", 
            (x) => if List.Contains(months, Date.AddMonths(Date.StartOfMonth(x[DATE]), -1)) then "Active" else "New")][status],
    group = Table.Group(#"Changed Type", "PRODUCT", {{"all", f}}),
    expand = Table.ExpandTableColumn(group, "all", {"DATE", "STATUS"})
in
    expand

[kittipunK]

"Dear AlienSx,

I have a question: Is it possible for me to add a column, Status2, to check the product of the current month versus the next month? If found, show 'Active'; if not, show 'Inactive.'

Thank you."

[kittipunK]

Thank you a lot, sir.