Extracting multiple values from one cell into new table and create filtering base on that

alocasia ,

We can do it in Power Query Editor,

1. Split this local shipping column using split by delimiter option.

2.Follow the same steps for International shipping column as well. You would get this result.

3. Select local shipping and international shipping then do unpivot columns.

4. Then select all the columns and remove duplicates,

Thanks,

Arul 

Hi alocasia ,
Try the below M Code:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8i9KSS1S8HRR0lHyyU9OzFEIzsgsKMjMSwcKeOaVpBblJZZk5uchS8TqRCtVVFTEGwKVODo66ig4OzvrKLi4uMD4Tk5OcEVGQEEgX0fB1dUVwlSKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#" " = _t, #"(blank)" = _t, #"(blank).1" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{" ", type text}, {"(blank)", type text}, {"(blank).1", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Order ID", type text}, {"Local Shipping", type text}, {"International Shipping", type text}}),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type1", {{"Local Shipping", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Local Shipping"),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Local Shipping", type text}}),
#"Split Column by Delimiter1" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type2", {{"International Shipping", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "International Shipping"),
#"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"International Shipping", type text}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type3", {"Order ID"}, "Attribute", "Value"),
#"Removed Duplicates" = Table.Distinct(#"Unpivoted Columns")
in
#"Removed Duplicates"

If this helps you then give it a kudos and accept this as a solution.