Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
93 | |
90 | |
84 | |
70 | |
49 |
User | Count |
---|---|
141 | |
120 | |
112 | |
60 | |
59 |