Forum Discussion

Shruthi96's avatar
Shruthi96
Helper III
3 years ago
Solved

Unique text split with top 4 categories

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 i...
Need Help
  • ThxAlot's avatar
    ThxAlot
    3 years ago

    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's avatar
    Ahmedx
    3 years ago
    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
Manoj_Nair's avatar
Manoj_Nair
Solution Supplier
3 years ago

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