Forum Discussion

BeccaD's avatar
BeccaD
New Member
4 years ago
Solved

Multiple headers on some columns based on column title prefix

Hi,

Very new to Power BI here, so please bare with me.

 

I am trying to build a report with a table visual based upon some data from SQL Server.

 

I have a table that looks similar to this:

 

 

And I need to present it more like this:

 

Is there a way to achieve this? All the examples I have seen on here seem to be based on Excel data and pivot tables, but I'm not really sure that applies in this instance.

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi BeccaD ,


    1.Add a Index column:

     

    2. Click Index column, select "Unpivot other columns":

     

    3. Click Attribute column, split it by "-":

     

    4. Rename columns.

     

    Below is the whole M syntax:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUUoC4mQgTgHiVCBOU4rViVZKB7IygDgHiDOBOAuIs5ViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, #"columnPrefix1-Column3" = _t, #"columnPrefix1-Column4" = _t, #"columnPrefix2-Column5" = _t, #"columnPrefix2-Column6" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"columnPrefix1-Column3", type text}, {"columnPrefix1-Column4", type text}, {"columnPrefix2-Column5", type text}, {"columnPrefix2-Column6", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Attribute.1", "Level1"}, {"Attribute.2", "Level2"}})
in
    #"Renamed Columns"

     

     5. Create a Matrix visual as shown below:

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi BeccaD ,


    1.Add a Index column:

     

    2. Click Index column, select "Unpivot other columns":

     

    3. Click Attribute column, split it by "-":

     

    4. Rename columns.

     

    Below is the whole M syntax:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUUoC4mQgTgHiVCBOU4rViVZKB7IygDgHiDOBOAuIs5ViYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, #"columnPrefix1-Column3" = _t, #"columnPrefix1-Column4" = _t, #"columnPrefix2-Column5" = _t, #"columnPrefix2-Column6" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"columnPrefix1-Column3", type text}, {"columnPrefix1-Column4", type text}, {"columnPrefix2-Column5", type text}, {"columnPrefix2-Column6", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", type text}, {"Attribute.2", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Attribute.1", "Level1"}, {"Attribute.2", "Level2"}})
in
    #"Renamed Columns"

     

     5. Create a Matrix visual as shown below:

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.