Forum Discussion
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", {{"Data de Modificação", each if List.Count(Text.ToList(Text.Select([Data de Modificação], ";"))) > List.Count(Text.ToList(Text.Select([OwnerTeam], ";"))) then Text.AfterDelimiter(_, ";")
As examples, this are the values of [OwnerTeam] and [Data de Modificação] first 3 lines:
[OwnerTeam] =
| Triagem Financeiro;Pré-Faturamento;Triagem Financeiro;Pré-Faturamento;Pré-Faturamento |
| Triagem Financeiro;Triagem Financeiro;Pré-Faturamento;Pré-Faturamento |
| Triagem Financeiro;Triagem Financeiro;Pré-Faturamento;Pré-Faturamento |
[Data de Modificação] =
| 2022-05-01T22:08:47.1760761;2022-05-01T22:08:48.4744222;2022-05-12T19:40:28.184647;2022-05-12T19:40:38.741046;2022-05-12T19:40:46.9951087;2022-05-13T13:59:03.0441615 |
| 2022-05-02T11:42:38.6517673;2022-05-02T11:42:41.0337359;2022-05-13T12:42:08.6606218;2022-05-13T12:42:19.6666306;2022-05-16T12:10:28.5007223 |
| 2022-05-02T11:45:29.4177818;2022-05-02T11:45:32.6880927;2022-05-13T12:40:05.2489342;2022-05-13T12:40:12.2450448;2022-05-16T12:08:16.3245471 |
What I'm hoping to achieve is for power query to verify if the amount of ";" in each line of columns [Data de Modificação] is greater or not then [OwnerTeam]; if it is, then I wish to remove everything before the first delimiter (in [Data de Modificação]). if not, I want the value to remain the same for each row of [Data de Modificação] column. The problem, for now, is that this step returns the following error:
Expression.Error: We cannot apply field access to the type Text.
Details:
Value=2022-05-01T22:08:47.1760761;2022-05-01T22:08:48.4744222;2022-05-12T19:40:28.184647;2022-05-12T19:40:38.741046;2022-05-12T19:40:46.9951087;2022-05-13T13:59:03.0441615
Key=Data de Modificação
It seems to me that the problem is related to the
List.Count(Text.ToList(Text.Select([OwnerTeam], ";")))
and
List.Count(Text.ToList(Text.Select([Data de Modificação], ";")))
(the Key changes according to what a put first in the if statement)
expression; when I try to create a new column with this formula, it works normally:
Table.AddColumn(#"Extracted Text After Delimiter3", "DelimiterAmount", each List.Count(Text.ToList(Text.Select([OwnerTeam], ";"))))
and
Table.AddColumn(#"change1", "DelimiterAmount2", each List.Count(Text.ToList(Text.Select([Data de Modificação], ";"))))
both work just fine, but when I try to use it in the if statement, it returns the error I mentioned earlier.
Does anyone knows what should I do differently? or is there another way I can try to create this if statement? I tried it outside the Table.TransformColumns() function, but it gave me a variety of different errors.
I have seen a relative similar question in this post https://community.powerbi.com/t5/Desktop/quot-Expression-Error-We-cannot-apply-field-access-to-the-type/m-p/401637 but couldn't figure out how to apply to my problem.
Thanks.
Here is the whole script:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rdI9bsMwDAXgqxSeY4F/Iil5z5zBW5DBKIzCQxzASA/Vc/RilTMErtWhQ1Z9IvD4pPO5ISBqIbaAPVEGz2IBTcEUu9o8iIkQ0dOQekxZIJMHdFGxmtiDCYJoTaIhpYjgmzHukXNMGTiACCrG5tD0yzR8jNe34zQP8/s4LbfutHx/tcfh/rkM13G+37r/3NkfNJfDpoQSC7PQmlhjqcG4q0wwALNxTL8i02pQ5hSU0GvDVEyVYVODroaP8iKAEfHfq75qs5gpBUEz3yR8GlNQd0hk+/SQIQYSTyxUG1KxWN7Kd5uVL4MauKAYvnKzyw8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Data de Modificação" = _t, OwnerTeam = _t]),
#"Extracted Text After Delimiter3" = Table.TransformColumnTypes(Source,{{"Data de Modificação", type text}, {"OwnerTeam", type text}}),
#"change1" = Table.AddColumn(#"Extracted Text After Delimiter3", "DelimiterAmount", each List.Count(Text.ToList(Text.Select([OwnerTeam], ";")))),
#"change2" = Table.AddColumn(#"change1", "DelimiterAmount2", each List.Count(Text.ToList(Text.Select([Data de Modificação], ";")))),
#"Extracted Text After Delimiter" = Table.TransformColumns(#"change2", {{"Data de Modificação", each if List.Count(Text.ToList(Text.Select([Data de Modificação], ";"))) > List.Count(Text.ToList(Text.Select([OwnerTeam], ";"))) then Text.AfterDelimiter(_, ";")
else _ }})
in
#"Extracted Text After Delimiter"
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
6 Replies
- CNENFRNL
Community Champion
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- ErisedAluremFrequent Visitor
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.
- CNENFRNL
Community 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