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

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's avatar
    lbendlin
    Super User
    2 years ago

    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