Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I have an excel file I am using within Power BI that has a load date in Row 1 of Column 2.
I have tried to use Create Custom Column using ``` = #"Changed Type"[Column2]{0} ``` to create a new column with just the value of Row 1. I would then fill down with this value to create a column that displays the load date for each row for use in calculations in the data.
When I try this I get an error: "Expression.Error: A cyclic reference was encountered during evaluation."
I have also attempted extracting the date, turning that into a table and then merging the new table back in, but have not been able to get this 2nd solution to work either.
Does anyone have a better solution that would work for this?
If further information would help I am happy to answer any questions for clarification.
Thank you
Solved! Go to Solution.
Hi @JSBDodson
I tried your second idea and found a workaround. You could refer to below steps.
1. Right click on the date cell and select Add as New Query.
2. Convert the new query Column2 to List rather than Table.
3. In original table, add a custom column = List.First(Column2). Then you will get the result.
What is strange is that if I extract the date from the original table directly by using "= SampleTable{0}[Column2]" and repeat the last two steps, I will get the same error "Expression.Error: A cyclic reference was encountered during evaluation." I guess if we extract the value from the same query or with reference to the query, it will display this cyclic reference error. However, if we extract the data from an independent query, this error will not appear.
Hope this helps.
Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.
Hi @JSBDodson , I find an easier method. Have a try!
= Table.AddColumn(#"Changed Type1", "Custom", each Record.Field(#"Changed Type1"{0}[[Column1]],"Column1"))
Regards,
Jing
Hi @JSBDodson
I tried your second idea and found a workaround. You could refer to below steps.
1. Right click on the date cell and select Add as New Query.
2. Convert the new query Column2 to List rather than Table.
3. In original table, add a custom column = List.First(Column2). Then you will get the result.
What is strange is that if I extract the date from the original table directly by using "= SampleTable{0}[Column2]" and repeat the last two steps, I will get the same error "Expression.Error: A cyclic reference was encountered during evaluation." I guess if we extract the value from the same query or with reference to the query, it will display this cyclic reference error. However, if we extract the data from an independent query, this error will not appear.
Hope this helps.
Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.
It would be a new step:
LoadDate = Table.AddColumn(PreviousStepName, "NewColumnName", each [Column2]{0})
"Could this be caused because I am trying to insert this step into query editor before some previously made queries?" - Not sure.
To debug, I suggest removing all steps(copy the text somewhere into notepad so you don't lose it) except the initial source and the new column step. See if that works.
When I try = #"Changed Type"[Column2]{0} I get "Expression.Error: A cyclic reference was encountered during evaluation."
The data I'm trying to pull is in Row 1 of Column2 (Column2 is the column name)
Could this be caused because I am trying to insert this step into query editor before some previously made queries?
I tried what you suggested = Table.AddColumn(#"PreviousStep", "NewColumn", each [Column1]{3})
I don't get any syntax error and the step is created, but the entire new column shows "Error".
Can you clarify why and help to resolve this ?
It should work if you've got the right reference to the previous step -> this bit #"Changed Type"
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
10 | |
8 | |
7 | |
6 | |
6 |