Return Specific value from a condition

[dt_2023]

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" column. Thanks in advance!

[dt_2023]

Thank you for your help! I tried using the code and it works fine as expected when i copy the code as is, however since I'm pulling data from multiple excel files from a folder and combining them, its showing me an error that the field 'ID' of the record wasn't found, when i click on the list in Output column.

Resource Table:

Thanks!

[lbendlin]

Did you first combine the binaries?

[dt_2023]

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

[lbendlin]

check the spelling.  Power Query is case sensitive.

[dt_2023]

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]

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

[dt_2023]

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:

[lbendlin]

not sure what you mean by "data or table columns".

Pick the ID and Output columns and do your comparisons.

[dt_2023]

Okay, so if you look at the last screenshot I provided, there is no ID column or any other column displayed from the Resource Table, it is just showing the Binary contenta and If i combine the files again, the Output column is not showing up.

[dt_2023]

Ok. I have a table called "Resources" that has a column called "ID" that has multiple licensing IDs (text) in each row. I have license ids in an excel sheet called "License Category" that are classified as "Inscope" and "out of scope"  I'd want to print the inscope ID values in another column "Output" by comparing the column ID from Resource table with the "License Category" sheet.

Resource Table:

License Category:

I have created a conditional column below, but i was able to only show what is inscope and outscope, but unable to display the specific inscope ID values.

=if Text.Contains([ID], "gpl-3.0") then "Inscope" else if Text.Contains([ID], "mpl-1.1") then "Inscope" else if Text.Contains([ID], "mpl-1.0") then "Inscope" else if Text.Contains([ID], "lgpl-3.0") then "Blacklisted"  else "outscope")

[lbendlin]

There's a lot going on in your source data - case mismatches, random spaces, strings that can be substrings of other strings.  All stuff strongly disliked by Power Query.

Resource:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci/I0TXWM9BRyAUyDPUMdSA0UCAdyDDSM1CK1YlWgknmpMOUY8oa6MAlc1BZhlB1MGGl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t]),
    InScope = Table.TransformColumns(Table.SelectRows(#"License category", each ([Category] = "Inscope")),{{"ID", Text.Lower, type text}})[ID],
    #"Added Custom" = Table.AddColumn(Source, "Output", each List.Intersect({List.Transform(Text.Split([ID],","), each Text.Lower(Text.Trim(_))),InScope})),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Output", each Text.Combine(List.Transform(_, Text.From), ","), type text})
in
    #"Extracted Values"

License category:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci/I0TXWM1DSUfLMK07OL0hVitWJVsoFihrqGWIVRVebk47VCJCoEVg0v7REIT9NAU2HIRa5WAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Category = _t])
in
    Source

[lbendlin]

That is too abstract. Provide some more realistic sample data and indiate the expected result.

Note that Power BI DAX does not support EVALUATE in columns or measures so this will have to be done in Power Query via Expression.Evaluate .