Forum Discussion

dt_2023's avatar
dt_2023
Frequent Visitor
2 years ago

Return Specific value from a condition

Hi All,   Can someone help me achieve the "Value" column based on the category from the below dataset. Let say Inscope values are A, C and E, I want to return ID's that are inscope in the "Value" c...
Need Help
dt_2023's avatar
dt_2023
Frequent Visitor
2 years ago

Yes, Binaries were first combined and then the above syntax provided was used.

lbendlin's avatar
lbendlin
Super User
2 years ago

check the spelling.  Power Query is case sensitive.

  • dt_2023's avatar
    dt_2023
    Frequent Visitor
    2 years ago

    Everything looks good from case sensitive side. so per my usecase, is the below screenshot expected after i use the syntax

    Table.AddColumn(Source, "Output",
    each List.Intersect({List.Transform(Text.Split([ID],","), each Text.Lower(Text.Trim(_))),Blacklist})) 

     

     

    Do i have to combine the binaries again? because while i'm extracting the values from the Output column, I see the below error.

     

    Expression.Error: The field 'ID' of the record wasn't found.
    Details:
    Content=[Binary]
    Name=XXX-2023-11-29-16-45-30.xlsx
    Extension=.xlsx
    Date accessed=12/6/2023 9:58:44 AM
    Date modified=12/4/2023 10:27:42 AM
    Date created=11/30/2023 9:52:05 AM
    Attributes=
    Content Type=application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
    Kind=Excel File
    Size=2218073
    ReadOnly=FALSE
    Hidden=FALSE
    System=FALSE
    Directory=FALSE
    Archive=TRUE
    Device=FALSE
    Normal=FALSE
    Temporary=FALSE
    SparseFile=FALSE
    ReparsePoint=FALSE
    Compressed=FALSE
    Offline=FALSE
    NotContentIndexed=FALSE
    Encrypted=FALSE
    ChangeTime=12/6/2023 9:58:44 AM
    SymbolicLink=FALSE
    MountPoint=FALSE
    Folder Path=XXXX

     

    • lbendlin's avatar
      lbendlin
      Super User
      2 years ago

      I think you need to expand the "Output"  lists.

      • dt_2023's avatar
        dt_2023
        Frequent Visitor
        2 years ago

        Thank you again for looking into this. I did expanded the Output column. The error that was occured previously about the ID wasn't found is now disappeared, I can see values in the Output column when i extract or expand the column. However, Firstly, I cannot see the data or table columns as shown in the below screenshot and is the last step in my power query applied steps and secondly, the Output column is just returning everything from the list and not filtering values based on category column.

        Resource Table: