Query Help - Create New Column with Value found in Row 1 of Column 2

[JSBDodson]

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

[v-jingzhang]

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

[v-jingzhang · Accepted 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.

[watkinnc]

It would be a new step:

LoadDate = Table.AddColumn(PreviousStepName, "NewColumnName", each [Column2]{0})

[HotChilli]

"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.

[JSBDodson]

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?

[DTV]

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 ?

[HotChilli]

It should work if you've got the right reference to the previous step -> this bit #"Changed Type"