Help - Power Query

[Anonymous]

Hi all,

I am a new using power query and I heve the error scenario when I try to execute a test with dataflow:

"Expression.Error: A cyclic reference was found during the evaluation."

This is my power query:

let

  Origem = OData.Feed("https://testbr.api.crm2.dynamics.com/api/data/v9.1/", null, [Implementation = "2.0"]),

  Navigation = Origem{[Name = "accounts", Signature = "table"]}[Data],

  #"Escolher colunas" = Table.SelectColumns(Navigation, {"accountnumber"}),

  #"Linhas filtradas" = Table.SelectRows(#"Escolher colunas", each [accountnumber] <> null and [accountnumber] <> ""),

  #"Linhas filtradas 1" = Table.SelectRows(#"Linhas filtradas", each Text.Contains([accountnumber], ".") or Text.Contains([accountnumber], "/") or Text.Contains([accountnumber], "-"))

  = Table.ReplaceValue(accounts,".","",Replacer.ReplaceText,{accountnumber}) or Table.ReplaceValue(accounts,"/","",Replacer.ReplaceText,{accountnumber})

or Table.ReplaceValue(accounts,"-","",Replacer.ReplaceText,{accountnumber})

in

  #"Linhas filtradas 1"

I am trying to take the special characters of a text (example: "24.462.349/0001-03" to "24462349000103"). But always receive the same error.

Thanks in advance!

Rafael Santos

[mahoneypat]

FYI that a quick way to strip the numbers out of a text column is to add a custom column with the Text.Select() function.

= Text.Select([Column1], {"0".."9"})

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[lbendlin]

This part

 #"Linhas filtradas 1" = Table.SelectRows(#"Linhas filtradas", each Text.Contains([accountnumber], ".") or Text.Contains([accountnumber], "/") or Text.Contains([accountnumber], "-"))
  = Table.ReplaceValue(accounts,".","",Replacer.ReplaceText,{accountnumber}) or Table.ReplaceValue(accounts,"/","",Replacer.ReplaceText,{accountnumber})
or Table.ReplaceValue(accounts,"-","",Replacer.ReplaceText,{accountnumber})

is malformed. Please review. The result is a logical comparison between SelectRows and ReplaceValue. Likely not what you intended.