The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
"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
Solved! Go to 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
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
Thank you sir, but I try use this formula still cyclic error. I m not sure how to solved it.
@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.
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?
@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
"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."
Thank you a lot, sir.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
39 | |
21 | |
20 | |
16 | |
13 |