Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code FABINSIDER for a $400 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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.
Solved! Go to 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.
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.
@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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Check out the February 2025 Power BI update to learn about new features.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
84 | |
69 | |
68 | |
39 | |
37 |