Table.AddColumn with a Expression.Error: A cyclic reference was found during the evaluation.

[Anonymous]

Hello

I have the following code but I got the Expression.Error: A cyclic reference was found during the evaluation. Thanks in advance for the help

= Table.AddColumn(#"Zephyr", "Custom", each if [a]="" then Text.Replace([Deal value SEK],",","") else [Deal value SEK])

The following table : 

[Vijay_A_Verma]

On the current dataset, I am not able to replicate your issue. Can you post your complete Query here? While you are in the editor here, press </> and paste the code.

[Anonymous]

Here is it

let
Källa = Excel.Workbook(File.Contents("G:\UDI\Databaser\Powerbi\Zephyr.xlsx"), null, true),
Results_Sheet = Källa{[Item="Results",Kind="Sheet"]}[Data],
#"Sammanfogade kolumner" = Table.CombineColumns(Table.TransformColumnTypes(Results_Sheet, {{"Column10", type text}, {"Column11", type text}}, "sv-SE"),{"Column10", "Column11"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Date"),
#"Upphöjda rubriker" = Table.PromoteHeaders(#"Sammanfogade kolumner", [PromoteAllScalars=true]),
#"Omdöpta kolumner" = Table.RenameColumns(#"Upphöjda rubriker",{{"Completed dateAssumed completion date", "Date"}}),
#"Infogat parsat datum" = Table.AddColumn(#"Omdöpta kolumner", "Parsa", each Date.From(DateTimeZone.From([Date])), type date),
#"Infogat år" = Table.AddColumn(#"Infogat parsat datum", "År", each Date.Year([Parsa]), Int64.Type),
#"Infogad månad" = Table.AddColumn(#"Infogat år", "Månad", each Date.Month([Parsa]), Int64.Type),
#"Infogat kvartal" = Table.AddColumn(#"Infogad månad", "Kvartal", each Date.QuarterOfYear([Parsa]), Int64.Type),
#"Ersatt värde" = Table.ReplaceValue(#"Infogat kvartal","n.a.","",Replacer.ReplaceValue,{"Deal value#(lf)SEK"}),
#"Dela upp kolumn efter avgränsare" = Table.SplitColumn(Table.TransformColumnTypes(#"Ersatt värde", {{"Deal value#(lf)SEK", type text}}, "sv-SE"), "Deal value#(lf)SEK", Splitter.SplitTextByDelimiter("*", QuoteStyle.Csv), {"Deal value#(lf)SEK.1", "Deal value#(lf)SEK.2"}),
#"Omdöpta kolumner1" = Table.RenameColumns(#"Dela upp kolumn efter avgränsare",{{"Deal value#(lf)SEK.2", "a"}, {"Deal value#(lf)SEK.1", "Deal value#(lf)SEK"}}),
#"Lägg till egen" = Table.AddColumn(#"Zephyr", "Custom", each if [a]="" then Text.Replace([Deal value SEK],",","") else [Deal value SEK])
in
#"Lägg till egen"

[Vijay_A_Verma]

I think I will have to request you to share your Excel without confidential/sensitive data through Onedrive/any other file hosting provider. 

[Anonymous]

Here is the link to the file

https://jumpshare.com/teams/invite/91832bf9f67655880963a9ae786e43f5

[Vijay_A_Verma]

Can't find anything there. Best is Onedrive or Google drive for share. 

[Anonymous]

Here is it

https://drive.google.com/drive/folders/1UVsCAWYuWouHFW0gVzymeYPqUcP8EfyO?usp=sharing

[Vijay_A_Verma · Accepted solution]

Your last line before in has to be following

    #"Lägg till egen" = Table.AddColumn(#"Omdöpta kolumner1", "Custom", each if [a]="" then Text.Replace([#"Deal value#(lf)SEK"],",","") else [#"Deal value#(lf)SEK"])

[Anonymous]

Big thanks.

I got also what I had wrong