Power M - Cyclic Reference encountered during evaluation

[smitpau]

Hi,

I'm trying to replace values based on the column indexed position using Table.ColumnNames. 

If I try the code below replacing the actual column name with the Table.ColumnNames version for some reason there is a cyclice reference error.

= Table.ReplaceValue(#"Removed Columns",null,"0",Replacer.ReplaceValue,{Table.ColumnNames(#"GPL adjustments"){4}, Table.ColumnName(#"GPL adjustments"){5}})

Any help is appreciated.

Thanks for reading.

[tex628]

Sorry, it doesn't appear to be as easy as i thought. I cant seem to figure out how to reference earlier steps in other queries. 

Essentially you can't reference the final step in GPL Adjustments because something in those queries is dependant on the querie you want to transform. 

What i wanted to do is reference the GPL queries before they become dependant but I think i need some more time to see how you reference correctly. 

Another solution that isn't as nice is breaking up the GPL queries before they become dependant by creating a new reference query just before you do the dependant transformation. 

Then you can turn off the load of the original queries, and then reference them in the replace transform without any errors.

/ J

[tex628]

You need to replace the table references with tables that occur before the transformation. Like this:

= Table.ReplaceValue(#"Removed Columns",null,"0",Replacer.ReplaceValue,Table.ColumnNames(#"Removed Columns")

If you want to reference GPL Adjustments 4 and 5 you need to make sure you reference them at a step where your current table has not affected the transformation stages yet. 

Br,
J

[smitpau · Accepted solution]

Thanks.

Think you were right the issue was the source was not correct.

I followed the video below, checked the syntax of the code for example below and it works.

Table.ReplaceValue(#"Removed Columns",null,"0",Replacer.ReplaceValue,{Table.ColumnNames(#"Removed Columns"){3}, Table.ColumnNames(#"Removed Columns"){4}, Table.ColumnNames(#"Removed Columns"){5}})

Refresh breaks due to COLUMN NAME CHANGES? 3 ways to fix changing column headers in Power BI! - YouT...

[smitpau]

Ah okay I think I see so it has to flow from the last transformation. 

So it would be like

{Table.ColumnNames(#"Removed Columns"){4}, Table.ColumnName(#"Removed Columns"){5}}