Forum Discussion

chris886's avatar
chris886
Frequent Visitor
6 years ago

Split column but still 'group' together?

My data export has a column with anywhere from 1-10 text entries, seperated by delimeters.  I split the column by delimeter, but I want to be able to analyze it as a whole group.  So basically if the row has a certain entry in any one of the 10 split columns, it will still show up in the visual.  I'm not having any luck accomplishing this and not sure if something needs to be done in power query or on the front end with the table values?    

 

I feel like what I'm trying to do is simple, but not sure what I'm missing.  Still a PBI beginner.  

Need Help

1 Reply

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    6 years ago

    Hi chris886 ,

     

    To unpivot the splited table as below.

     

     

    Then we can get the result as below.

     

    M code for your reference.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUUrUSdZJSdFJS9NJT9cpKirSSSxOAeLiYqXYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [catgory = _t, term = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"catgory", type text}, {"term", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "term", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"term.1", "term.2", "term.3", "term.4", "term.5", "term.6", "term.7", "term.8"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"term.1", type text}, {"term.2", type text}, {"term.3", type text}, {"term.4", type text}, {"term.5", type text}, {"term.6", type text}, {"term.7", type text}, {"term.8", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"catgory"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"

     

    If it doesn't meet your requirement, kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.