Forum Discussion
Solved
Expression.Error: We cannot apply field access to the type Text
Hello everyone! I need some help with an if statement in power query. The formula I'm using currently is as shown below: #"Extracted Text After Delimiter" = Table.TransformColumns(#"change2",...
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZI5bsMwEEWvEqi2iNk4w6XKAtcp1AkuhEAIXFgGBPtQOUcuFiqFIYspUrjl4wfe/2TfN5gpc5bsm13znF/ya35rDru+ISBqwbeAHVGCkMQcmoJpSVQsODERIroxpA5jEkgUHAZRsRpxcCYIojUSdTF6hLCKcYecfEzADkRQcXHu5uPwOZ6e9sdpmD7G43zO7/P3V7sfLtd5OI3T5Zz/c2d7cD9C0cIktBirLzMY54oJOmA29vFOmRYGJaeghKFmGAtTZVjNoAvD3/E8gBHx31Uf1cwnik7QLKwMb4zJaQgQybb2kMA7khBZqGZIhfnyVmHTrHwZVMcFiuEjmx1+AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Data de Modificação" = _t, OwnerTeam = _t]), Removed = Table.ReplaceValue(Source, each List.Count(Text.Split([OwnerTeam], ";")), null, (x,y,z) => if List.Count(Text.Split(x, ";")) > y then Text.AfterDelimiter(x, ";") else x, {"Data de Modificação"}) in Removed
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("xZM9asNAEIWvElRby/ztzP5UScB1CnXGhQgiuLAMwjlUzpGLZRWDUbQuQkhIs8V8O+x7b3Z2u4aAqAXfAnZECUISc2gKpphrFpyYCBFdGVKHMQkkCg6DqFizae7zQzmx2W/+4oEacXAmCKI1EnUxeoSwaOMOOfmYgB2IoKIvarvp0L8Mx7vtYezH5+EwnfLT9P7Wbvvz69Qfh/F8yt+5sy7UQRRpmIRm1epLFMa5YoIOmI19/CKbZgalT0EJQ80wFqbKsIhCZ4afAXoAI+Lbdn/oDm6584miEzQLC5VXxuQ0BIhkaweQwDuSEFmoZkiF+TKzsHJXvg6q4wLF8Ffd/cvsLiuUHy/p7j8A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Data de Modificação" = _t, OwnerTeam = _t, Testeteste = _t]),
Removed = Table.ReplaceValue(Source,
each List.Count(Text.Split([OwnerTeam], ";")),
each [Testeteste],
(x,y,z) =>
if List.Count(Text.Split(x, ";")) > y then
if [Testeteste] = 0 then
Text.AfterDelimiter(x, ";")
else Text.BeforeDelimiter(x, ";", {0,RelativePosition.FromEnd})
else x,
{"Data de Modificação"})
in
Removed
Thanks for the reply!
Unfortunetly it isn't working, step Removed returns the same result as the step Source.
It's only a correction of your syntax error. Parts of expression were copied from your code snippet. Try by yourself to alter the "if ... then" logic part if you mean to make progress.
I think I got it, I needed to make a small change in two places:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("xZM9asNAEIWvElRby/ztzP5UScB1CnXGhQgiuLAMwjlUzpGLZRWDUbQuQkhIs8V8O+x7b3Z2u4aAqAXfAnZECUISc2gKpphrFpyYCBFdGVKHMQkkCg6DqFizae7zQzmx2W/+4oEacXAmCKI1EnUxeoSwaOMOOfmYgB2IoKIvarvp0L8Mx7vtYezH5+EwnfLT9P7Wbvvz69Qfh/F8yt+5sy7UQRRpmIRm1epLFMa5YoIOmI19/CKbZgalT0EJQ80wFqbKsIhCZ4afAXoAI+Lbdn/oDm6584miEzQLC5VXxuQ0BIhkaweQwDuSEFmoZkiF+TKzsHJXvg6q4wLF8Ffd/cvsLiuUHy/p7j8A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Data de Modificação" = _t, OwnerTeam = _t, Testeteste = _t]), Removed = Table.ReplaceValue(Source, each List.Count(Text.Split([OwnerTeam], ";")), each [Testeteste], (x,y,z) => if List.Count(Text.Split(x, ";")) > y then if z = "0" then Text.AfterDelimiter(x, ";") else Text.BeforeDelimiter(x, ";", {0,RelativePosition.FromEnd}) else x, {"Data de Modificação"}) in RemovedI changed [Testeteste] inside the if clause by "z", and compared to 0 in string form, instead of as a number. Thank you for your help, I definently wouldn't have done it without your help!