Forum Discussion
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" column. Thanks in advance!
12 Replies
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 .
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")
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
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!
