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)

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
8 years ago

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's avatar
    powerbi_seek
    Frequent Visitor
    8 years ago

    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's avatar
      alena2k
      Icon for Resolver IV rankResolver IV
      8 years ago

      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'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