Multiple headers on some columns based on column title prefix

[BeccaD]

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.

[v-eqin-msft · Accepted solution]

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.

[amitchandak]

@BeccaD , You might have to unpivot the columns and split that. Or dynamic segmentation

refer

column header grouping
https://www.daxpatterns.com/dynamic-segmentation/
https://community.powerbi.com/t5/Desktop/Matrix-Display-Values-above-Columns/td-p/256905
https://community.powerbi.com/t5/Desktop/grouping-measure-results/td-p/456795