How to query a column in a different query which has multiple values?

Hi, Anonymous ;

You could use power query and the steps are as follows:

1.split column by ",".

2.Press “Shift” to select all columns that have been separated, and unpivot it.

3.Remove the "Attribute" column ,then remove the empty rows.

The final output is shown below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RY/NbsJADIRfZbRnH/pD4R5aCQEnNuJQlIOVbMHC2UXLJihvX9NW5eaxPJ9nDgf3/PY6d+TWHAPqlEfWzqRPX+XGOdhYS489t0zwrNhx7AjvHFEPKoStKFZpKoR1wkbikfDJ7RnbEO6rCVVQJRBcQ/ZsMXsxZJVDKfC9lJOpZYrXQYt54UMepQ1X236MARXnfPf+E/8giyc72Bh4gm9PvXTlt4GUlMVC+uFyUfnB0CODP9ukg+Xa8WQVbq5pvgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [RequestID = _t, Requestor = _t, RequestType = _t, Assignee = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"RequestID", Int64.Type}, {"Requestor", type text}, {"RequestType", type text}, {"Assignee", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Assignee", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Assignee.1", "Assignee.2", "Assignee.3", "Assignee.4", "Assignee.5", "Assignee.6", "Assignee.7", "Assignee.8", "Assignee.9"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Assignee.1", type text}, {"Assignee.2", type text}, {"Assignee.3", type text}, {"Assignee.4", type text}, {"Assignee.5", type text}, {"Assignee.6", type text}, {"Assignee.7", type text}, {"Assignee.8", type text}, {"Assignee.9", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"RequestID", "Requestor", "RequestType"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Columns"," ","",Replacer.ReplaceText,{"Value"}),
    #"Filtered Rows" = Table.SelectRows(#"Replaced Value", each [Value] <> null and [Value] <> "")
in
    #"Filtered Rows"

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.