How to filter a column with strings of product categories

[Anonymous]

Dear Community

I have a dataset with a column that is a string of many products:

Shipment	Products (string)
1	Apple, Banana, Pear, Melon, Grapes
2	Kiwi
3	Banana, Pear
4	Orange, Berry, Mango, Grapes

I need to be able to filter the data set based on the products, so I need to be able to have a filter where the user can choose 'Grapes', and then only shipment 1 and 4 is shown. There are as many products types as there is types of fruit (so a lot), how can I filter such data set with such a column that have merged products in one column?

Thank you very much in advance. Always appreciate the help in here 🙂

Sincerely, Signe

[shafiz_p · Accepted solution]

Hi @Anonymous You can split Products (String) column by comma (,) delimiter in power query. Then select column Shipment, and unpivot other columns. This way you can filter product as you want. For example, select graphs and it will show only shipment 1, and 4. Use below example codes to solve the problem :

let

Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

TypeChange = Table.TransformColumnTypes(Source,{{"Shipment", Int64.Type}, {"Products (string)", type text}}),

SplitColumnbyDelimiter = Table.SplitColumn(
TypeChange,
"Products (string)",
Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv),
{"Products (string).1", "Products (string).2", "Products (string).3", "Products (string).4", "Products (string).5"}
),

UnpivotOtherColumn = Table.UnpivotOtherColumns(SplitColumnbyDelimiter, {"Shipment"}, "Attribute", "Value"),
RemovedColumns = Table.RemoveColumns(UnpivotOtherColumn,{"Attribute"}),
RenamedColumns = Table.RenameColumns(RemovedColumns,{{"Value", "Products"}}),
TrimmedText = Table.TransformColumns(RenamedColumns,{{"Products", Text.Trim, type text}})

in
TrimmedText

Hope this helps!!

If this solved your problem, please mark it as a solution!!

[Anonymous · Accepted solution]

Hi, @shafiz_p 

thanks for your concern about this issue.  

I am in favor of your idea.  

Hi, @Anonymous 

I am glad to help you.   

According to your description, you want to know how to filter a column with strings of product categories? 

If I understand you correctly, then you can refer to my solution.  

First, you can use New table in Power BI Desktop to create a table with all the products: Separate the products in the table with ',' and then distinct the separated products. 

ProductsTable = 
DISTINCT (
    SELECTCOLUMNS (
        ADDCOLUMNS (
            GENERATESERIES (
                1,
                PATHLENGTH (
                    SUBSTITUTE ( CONCATENATEX ( Table1, Table1[Product(string)], "," ), ",", "|" )
                )
            ),
            "Product",
                TRIM (
                    PATHITEM (
                        SUBSTITUTE ( CONCATENATEX ( Table1, Table1[Product(string)], "," ), ",", "|" ),
                        [Value]
                    )
                )
        ),
        "Product", [Product]
    )
)

Then create a Measure to determine if the value selected by Slicer exists in Product(string): 

MEASURE = 
VAR searchvalue =
    SELECTEDVALUE ( ProductsTable[Product] )
RETURN
    IF (
        CONTAINSSTRING (
            CONCATENATEX ( Table1, Table1[Product(string)], "," ),
            searchvalue
        ),
        1,
        0
    )

Finally apply Measure to visual: 

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[shafiz_p · Accepted solution]

Hi @Anonymous You can split Products (String) column by comma (,) delimiter in power query. Then select column Shipment, and unpivot other columns. This way you can filter product as you want. For example, select graphs and it will show only shipment 1, and 4. Use below example codes to solve the problem :

let

Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

TypeChange = Table.TransformColumnTypes(Source,{{"Shipment", Int64.Type}, {"Products (string)", type text}}),

SplitColumnbyDelimiter = Table.SplitColumn(
TypeChange,
"Products (string)",
Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv),
{"Products (string).1", "Products (string).2", "Products (string).3", "Products (string).4", "Products (string).5"}
),

UnpivotOtherColumn = Table.UnpivotOtherColumns(SplitColumnbyDelimiter, {"Shipment"}, "Attribute", "Value"),
RemovedColumns = Table.RemoveColumns(UnpivotOtherColumn,{"Attribute"}),
RenamedColumns = Table.RenameColumns(RemovedColumns,{{"Value", "Products"}}),
TrimmedText = Table.TransformColumns(RenamedColumns,{{"Products", Text.Trim, type text}})

in
TrimmedText

Hope this helps!!

If this solved your problem, please mark it as a solution!!