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 (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.

  • 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)

6 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super 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
        Resolver 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.

  • alena2k's avatar
    alena2k
    Resolver 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"),