Expression.Error: We cannot apply field access to the type Text

[ErisedAlurem]

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-t... 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"

[CNENFRNL · Accepted solution]

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

[ErisedAlurem]

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
    Removed

Explaining 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]

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

[ErisedAlurem]

Thanks for the reply!

Unfortunetly it isn't working, step Removed returns the same result as the step Source.

[CNENFRNL]

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.

[ErisedAlurem]

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
    Removed

I 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!