Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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

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 : 

  • 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"])

8 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Icon for Most Valuable Professional rankMost Valuable Professional

    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's avatar
      Anonymous
      Not applicable

      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's avatar
        Vijay_A_Verma
        Icon for Most Valuable Professional rankMost Valuable Professional

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