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
Mkarwa-123's avatar
Mkarwa-123
Resolver II
3 years ago

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"