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)

powerbi_seek's avatar
powerbi_seek
Frequent Visitor
8 years ago

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's avatar
powerbi_seek
Frequent Visitor
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)