Forum Discussion

powerbi_seek's avatar
powerbi_seek
Frequent Visitor
8 years ago
Solved

PBI - split multiple value to rows in a table

Hello guys,   I have a specific need please. My pbix is sourcing on a SSAS Tabular cube, in this latest, I have a column, with multiple values. I want to split those values by rows in a new table ...
  • powerbi_seek's avatar
    powerbi_seek
    8 years ago

    Hi,

     

    As I said, it should be done on the SSAS retreive query of my table.

     

    I implemented this :

    SELECT T1.reference, T1.name, T2.my_Splits AS subjects, T1.subtitileFROM
 (
  SELECT *,  CAST('<X>'+replace(T.subjects,',','</X><X>')+'</X>' as XML) as my_Xml 
  FROM [yourTable] T
 ) T1
 CROSS APPLY
 ( 
 SELECT my_Data.D.value('.','varchar(50)') as my_Splits
 FROM T1.my_Xml.nodes('X') as my_Data(D)
 ) T2

     (source : https://stackoverflow.com/questions/13159526/split-values-over-multiple-rows)

alena2k's avatar
alena2k
Icon for Resolver IV rankResolver IV
8 years ago

 

If you can use Query Editor, it is very easy:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUaqwrrSuUorViVYyAvISrZOUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "ID"}, {"Column2", "Values"}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Renamed Columns", "Values", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Values.1", "Values.2", "Values.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Values.1", type text}, {"Values.2", type text}, {"Values.3", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"ID"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"})
in
    #"Removed Columns"

 You really only need to connect to your source, split column and unpivot using Query Editor UI:
...

"Split Column by Delimiter" = Table.SplitColumn(#"Renamed Columns", "Values", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Values.1", "Values.2", "Values.3"}),

....

 

    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"ID"}, "Attribute", "Value"),