PBI - split multiple value to rows in a table

[powerbi_seek]

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 (guess calculated), whi allow me to navigate and drill on these datas.

Example :

   Table 1

   ID                Values

   1                 x;y;z

   2                 a;b

Table Custom:

ID      Value

1        x

1        y

1        z

2        a

2        b

Thanks a lot for your time,

regards.

[Ashish_Mathur]

Hi,

In the Query Editor, when you go to the Split column window, under Advanced Options, there is an option to split by rows.

[powerbi_seek]

Thanks for your feedbacks.

But the problem is I m using a direct connection to Tabular SSAS, so I guess that I can't use Query editor. isn't it ?

[alena2k]

Sorry, I missed that it is direct connection. I cannot imagine how to solve it in DAX... will wait for solution with you.

[powerbi_seek]

Hello,

thank anyway.

I think that it should be done at the source in SSAS, there is a query that retreive data when you map the project on the cube.

For now, it's just an idea, I will let you know.

Thanks

[powerbi_seek · Accepted solution]

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]

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"),