Unique text split with top 4 categories

[Shruthi96]

Hi Folks, 

I am currently seeking a solution for my project within Power BI, utilizing DAX code. Within this endeavor, I possess a table containing the information outlined below. The task at hand involves segmenting this data by comma, extracting the highest four counts, and subsequently presenting the corresponding subcategories in a comma-separated format.

Category	Sub Category
Category 1	AAA
Category 2	BBB,CCC,AAA
Category 3	DDD,EEE,ZZZ
Category 4	ZZZ
Category 1	AAA
Category 1	BBB
Category 2	YYY
Category 3	EEE
Category 4	GGG
Category 2	ZZZ,DDD
Category 1	SSS
Category 2	OOO
Category 3	PPP,RRR
Category 4	JJJ,KKK
Category 3	LLL,WWW
Category 3	DDD,EEE,ZZZ
Category 3	DDD,EEE,ZZZ
Category 3	DDD,EEE,ZZZ
Big list continues

Required output in below form below, 

	Top 4
Category 1	AAA,BBB,SSS
Category 2	BBB,CCC,AAA,OOO
Category 3	DDD,EEE,ZZZ
Category 4	GGG,JJJ,KKK,ZZZ

Thanks 

[ThxAlot · Accepted solution]

I don't seem anything to do with DAX but only some tricky transformation by PQ

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ldA9CsMwDAXgqxTPWtL2Av7DYBts7MHYIUOHkjFQuuT2EXRpeV2yvifxCc2z0I/3c91e+2USJKSUYqGv8MqhUoq01gTljUtjDFlraYzxW965hPCvMX0MhHvvCDKGkHMO11knPg+xWitOp5QQyzlTKQVB7z2FEHAjxkittROPOlcuBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, #"Sub Category" = _t]),

    Grouped = Table.Group(
        Source,
        "Category",
        {"TOP4", each let
            sorted = Table.Sort(_,{{"Sub Category", Order.Ascending}}),
            combined = Text.Combine(List.Distinct(List.FirstN(sorted[Sub Category],4)),","),
            truncated = Text.BeforeDelimiter(combined, ",", 3)
            in truncated
        })
in
    Grouped

[Ahmedx]

pls try rhis

Measure = 

VAR _text =CONCATENATEX(DISTINCT(SUMMARIZE('Table','Table'[Category ],'Table'[Sub Category])),SUBSTITUTE('Table'[Sub Category],",","|"),"|",SUBSTITUTE('Table'[Sub Category],",","|"),ASC)
RETURN 
PATHITEM(_text,1) &","&PATHITEM(_text,2) &","&  PATHITEM(_text,3) &","&PATHITEM(_text,4)

[Shruthi96]

Thank you. 

Is there is a way to remove "," incase if there is no 3rd or 4th text. 

Example: 

Category 1

AAA,,,

[Ahmedx]

and try this

Measure 4 = 

VAR _text =CONCATENATEX(DISTINCT(SUMMARIZE('Table','Table'[Category ],'Table'[Sub Category])),SUBSTITUTE('Table'[Sub Category],",","|"),"|",SUBSTITUTE('Table'[Sub Category],",","|"),ASC)
VAR t1 = {PATHITEM(_text,1),PATHITEM(_text,2),PATHITEM(_text,3),PATHITEM(_text,4)}
RETURN 
CONCATENATEX(FILTER(t1,[Value]<> BLANK()),[Value],", ",[Value],ASC)

[Ahmedx]

pls try this

Measure 2 = 

VAR _text =CONCATENATEX(DISTINCT(SUMMARIZE('Table','Table'[Category ],'Table'[Sub Category])),SUBSTITUTE('Table'[Sub Category],",","|"),"|",SUBSTITUTE('Table'[Sub Category],",","|"),ASC)
VAR t1 = if(NOT ISBLANK(PATHITEM(_text,1)),PATHITEM(_text,1)&",")
VAR t2 = if(NOT ISBLANK(PATHITEM(_text,2)),PATHITEM(_text,2)&",")
VAR t3= if(NOT ISBLANK(PATHITEM(_text,3)),PATHITEM(_text,3)&",")
VAR t4 = if(NOT ISBLANK(PATHITEM(_text,4)),PATHITEM(_text,4))
RETURN 
t1&t2&t3&t4

[Shruthi96]

Thank you for replying, I tried both the options. However, it gives repeated values.  Like, AAA,AAA,AAA,AAA

[Ahmedx]

I fixed my mistake, please try again

[Shruthi96]

code please

[Ahmedx · Accepted solution]

Measure 4 = 

VAR _text =CONCATENATEX(DISTINCT(SUMMARIZE('Table','Table'[Category ],'Table'[Sub Category])),SUBSTITUTE('Table'[Sub Category],",","|"),"|",SUBSTITUTE('Table'[Sub Category],",","|"),ASC)
VAR t1 = {PATHITEM(_text,1),PATHITEM(_text,2),PATHITEM(_text,3),PATHITEM(_text,4)}
RETURN 
CONCATENATEX(FILTER(t1,[Value]<> BLANK()),[Value],", ",[Value],ASC)

Measure 2 = 

VAR _text =CONCATENATEX(DISTINCT(SUMMARIZE('Table','Table'[Category ],'Table'[Sub Category])),SUBSTITUTE('Table'[Sub Category],",","|"),"|",SUBSTITUTE('Table'[Sub Category],",","|"),ASC)
VAR t1 = if(NOT ISBLANK(PATHITEM(_text,1)),PATHITEM(_text,1)&",")
VAR t2 = if(NOT ISBLANK(PATHITEM(_text,2)),PATHITEM(_text,2)&",")
VAR t3= if(NOT ISBLANK(PATHITEM(_text,3)),PATHITEM(_text,3)&",")
VAR t4 = if(NOT ISBLANK(PATHITEM(_text,4)),PATHITEM(_text,4))
RETURN 
t1&t2&t3&t4

[Shruthi96]

Super Awesome 🙂 thank you @Ahmedx 

[Mkarwa-123]

Hi @Shruthi96 
Use power query for this.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ldA9CsMwDAXgqxTPWtL2Av7DYBts7MHYIUOHkjFQuuT2EXRpeV2yvifxCc2z0I/3c91e+2USJKSUYqGv8MqhUoq01gTljUtjDFlraYzxW965hPCvMX0MhHvvCDKGkHMO11knPg+xWitOp5QQyzlTKQVB7z2FEHAjxkittROPOlcuBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Category " = _t, #"Sub Category" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Category ", type text}, {"Sub Category", type text}}),
#"Removed Duplicates" = Table.Distinct(#"Changed Type"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Removed Duplicates", "Sub Category", Splitter.SplitTextByDelimiter(",", QuoteStyle.None), {"Sub Category.1", "Sub Category.2", "Sub Category.3"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Sub Category.1", type text}, {"Sub Category.2", type text}, {"Sub Category.3", type text}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Category "}, "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
#"Removed Duplicates1" = Table.Distinct(#"Removed Columns"),
#"Grouped Rows" = Table.Group(#"Removed Duplicates1", {"Category "}, {{"All", each Text.Combine(List.Distinct([Value]),", ")}})
in
#"Grouped Rows"

[Manoj_Nair]

@Shruthi96- Please check this out, let me know if this works. If this fix your problem, please tick this a solution and a thumps up. 

let
    // Load the source data
    Source = Table.FromRecords({
        [Category="Category 1", SubCategory="AAA"],
        [Category="Category 2", SubCategory="BBB,CCC,AAA"],
        [Category="Category 3", SubCategory="DDD,EEE,ZZZ"],
        [Category="Category 4", SubCategory="ZZZ"],
        [Category="Category 1", SubCategory="AAA"],
        [Category="Category 1", SubCategory="BBB"],
        [Category="Category 2", SubCategory="YYY"],
        [Category="Category 3", SubCategory="EEE"],
        [Category="Category 4", SubCategory="GGG"],
        [Category="Category 2", SubCategory="ZZZ,DDD"],
        [Category="Category 1", SubCategory="SSS"],
        [Category="Category 2", SubCategory="OOO"],
        [Category="Category 3", SubCategory="PPP,RRR"],
        [Category="Category 4", SubCategory="JJJ,KKK"],
        [Category="Category 3", SubCategory="LLL,WWW"],
        [Category="Category 3", SubCategory="DDD,EEE,ZZZ"],
        [Category="Category 3", SubCategory="DDD,EEE,ZZZ"],
        [Category="Category 3", SubCategory="DDD,EEE,ZZZ"]
    }),

    // Expand the SubCategory values
    SplitSubCat = Table.ExpandListColumn(Table.TransformColumns(Source, {{"SubCategory", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), type list}}), "SubCategory"),

    // Group by Category and aggregate SubCategory values
    GroupedRows = Table.Group(SplitSubCat, {"Category"}, {{"AllData", each _, type table [Category=text, SubCategory=text]}}),

    // Extract unique SubCategory values and transform back into comma-separated format
    TransformToList = Table.AddColumn(GroupedRows, "SubCategory", each Text.Combine(List.Distinct(Table.Column([AllData], "SubCategory")), ",")),

    // Remove unnecessary columns
    FinalTable = Table.SelectColumns(TransformToList, {"Category", "SubCategory"})
in
    FinalTable

[ThxAlot · Accepted solution]

I don't seem anything to do with DAX but only some tricky transformation by PQ

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ldA9CsMwDAXgqxTPWtL2Av7DYBts7MHYIUOHkjFQuuT2EXRpeV2yvifxCc2z0I/3c91e+2USJKSUYqGv8MqhUoq01gTljUtjDFlraYzxW965hPCvMX0MhHvvCDKGkHMO11knPg+xWitOp5QQyzlTKQVB7z2FEHAjxkittROPOlcuBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t, #"Sub Category" = _t]),

    Grouped = Table.Group(
        Source,
        "Category",
        {"TOP4", each let
            sorted = Table.Sort(_,{{"Sub Category", Order.Ascending}}),
            combined = Text.Combine(List.Distinct(List.FirstN(sorted[Sub Category],4)),","),
            truncated = Text.BeforeDelimiter(combined, ",", 3)
            in truncated
        })
in
    Grouped

[ToddChitt]

Why does Category 2 not include YYY? It is the 7th entry in your table. Should the result not be:

Category 2 | BBB,CCC,AAA,OOO,YYY

?

I think in Power Query (not DAX) you have the ability to split into Rows

That breaks it down to pairs records. You then need to combine them back. Not sure how to do that yet, but working on it.