Forum Discussion

ErisedAlurem's avatar
ErisedAlurem
Frequent Visitor
4 years ago
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", {{"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's avatar
    CNENFRNL
    Icon for Community Champion rankCommunity 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

    • ErisedAlurem's avatar
      ErisedAlurem
      Frequent 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
          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's avatar
        CNENFRNL
        Icon for Community Champion rankCommunity 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