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...
Need Help
v-frfei-msft's avatar
v-frfei-msft
Icon for Community Support rankCommunity 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.