Forum Discussion
Expression.Error: We cannot apply field access to the type Text
- 4 years ago
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("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
Firstly, thanks for the answer, it was on the spot!
Unfortunetly I need to add a new condition, and once again I find myself stuck, and having trouble to figure out things on my own.
If I want to add a new condition, based on the value of another column, how should I do it? I tried to change the function like below, adding a condition that (should) verify if the value of column [Testeteste] is 0 or 1:
Removed = Table.ReplaceValue(#"Added Custom",
each List.Count(Text.Split([OwnerTeam], ";")),
null,
(x,y,z) =>
if List.Count(Text.Split(x, ";")) > y then
each if [Testeteste] = 0 then
Text.AfterDelimiter(x, ";") else
Text.BeforeDelimiter(x, ";"
, {0,RelativePosition.FromEnd}
) else
x
,
{"Data de Modificação"})but instead of returning any value at all, it returns a function:
I tried changing the order a little bit, (for instance, I tried "if each", instead of "each if"; it stopped returning a function, but the result was not as expected)
Here is the full query:
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]),
#"Added Custom" = Table.AddColumn(Source, "teet", each Text.ToList("1234")),
Removed = Table.ReplaceValue(#"Added Custom",
each List.Count(Text.Split([OwnerTeam], ";")),
null,
(x,y,z) =>
if List.Count(Text.Split(x, ";")) > y then
each if [Testeteste] = 0 then
Text.AfterDelimiter(x, ";") else
Text.BeforeDelimiter(x, ";"
, {0,RelativePosition.FromEnd}
) else
x
,
{"Data de Modificação"})
in
RemovedExplaining a little bit more, first I want to verify if the amount of ";" in [OwnerTeam] is equals to the amount in [Data de Modificação], if it is, then nothing is done, if it is not, then it should verify if [Testeteste] column value (for each line in it) is equals to 0; if it is, then it should only get the text after the first delimiter; if not, it should get the text only until the last delimiter.
I'm not sure if a should start a new topic about it, since my original problem was solved.
Any help would be much appreciated.
- CNENFRNL4 years agoCommunity Champion
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- ErisedAlurem4 years agoFrequent Visitor
Thanks for the reply!
Unfortunetly it isn't working, step Removed returns the same result as the step Source.
- CNENFRNL4 years agoCommunity Champion
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.