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 Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.