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",...
Need Help
  • CNENFRNL's avatar
    CNENFRNL
    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

CNENFRNL's avatar
CNENFRNL
Community Champion
4 years ago

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's avatar
ErisedAlurem
Frequent Visitor
4 years ago

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!