Forum Discussion
Count and visualize data cells which contain several values (products)
Hi,
Please read up on the CONCATENATEX() function.
Hi Anonymous ,
I suggest you to duplicate the [Products sold] in Power Query Editor and then split the duplicated one.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8swtyEnNTc0rSU1R0lEKKMrPSk0uUXCEsFNKoWy//KKSjNSiPAXX0qL8glSgSKChUqwOLu1OSNqdrBWQTQrOL0U3yQhskn9een5mXjqSKc7IjkCYgmQgSEV4anEJ8S5zQXYZVt1A18QCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Status = _t, Project = _t, #"Products sold" = _t, Region = _t, #"Reporting Period" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Status", type text}, {"Project", type text}, {"Products sold", type text}, {"Region", type text}, {"Reporting Period", type text}}), #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Products sold", "Products sold - Copy"), #"Reordered Columns" = Table.ReorderColumns(#"Duplicated Column",{"Status", "Project", "Products sold", "Products sold - Copy", "Region", "Reporting Period"}), #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Reordered Columns", {{"Products sold - Copy", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Products sold - Copy"), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Products sold - Copy", type text}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Products sold - Copy", "Products sold -Splited"}}), #"Trimmed Text" = Table.TransformColumns(#"Renamed Columns",{{"Products sold -Splited", Text.Trim, type text}}) in #"Trimmed Text"Then create a measure to achieve your goal.
Products 2 = IF(CONTAINSSTRING(MAX('Table'[Products sold]),";"),"Multi-product project",MAX('Table'[Products sold]))Result is as below.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you for your response. Your solutions worked well, but I have another issue that emerged because I have slightly changed the table view. I have a column now which lists all the products sold for a specific project in one row like displayed below (Products combined). I would like to create another customized column which checks the products combined column and returns values based on the following:
- If products combined column is empty, return empty
- If products combined column contains exactly "Product A,Product B", return Product A including B
- If products combined column contains at least two of the following words/products (Product A, Product B, Product C, Product D), return Solution Project
- Otherwise return value of Products combined column.
I have tried to use the formula further down below, but apparently there is a mistake in the third IF statement and the result is not displayed correctly (see below in red). Would be great if you could advise what the mistake is here? If there is an easier way to achieve the expected result, please let me know. Thank you!
| ID | Products combined | Product Reporting (current result) | Product Reporting (expected result) |
| 1 | Product A | Product A | Product A |
| 2 | Product A,Product B | Product A including Product B | Product A including Product B |
| 3 | Product A,Product B,Product C, Product D | Product A,Product B,Product C, Product D | Solution Project |
| 4 | Product A,Product F | Product A,Product F | Product A,Product F |
IF(
ISBLANK('Dashboard'[ProductsCombined]),
BLANK(),
IF(
'Dashboard'[ProductsCombined] = "Product A,Product B",
"Product A including Product B",
IF(
CALCULATE(
COUNTROWS(
FILTER(
VALUES('Dashboard'[ProductsCombined]),
COUNTROWS(
INTERSECT(
VALUES('Dashboard'[ProductsCombined]),
{"Product A", "Product B", "Product C", "Product D", "Product E", "Product F", "Product G"}
)
)
)
)
) >= 2,
"Solution Project",
'Dashboard'[ProductsCombined]
)
)
)