Forum Discussion
Matrix table not displaying all the values
I need Value 1 and Value 2 to be the row header and the numerical values to be the column values. Using the table will result in Value1 and Value2 being displayed as a long list of rows and the respective values to it. I want Value 1 and Value 2 to be the row header and all the values as below the header
Hi TAN_THOMAS - You can acheive this in power query editor as below steps:
Create an index column in table, Next do a pivot by selecting the Column1 and you can see the below snapshot for pivot column choose the below configuration and output you will see as expected row header with Value 1 and value 2.
final result should be as follows:
Once you achieve the output you can remove the index column if it is not required.
Advanced query output:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkvMKU01VNJRMjQwUIrVIUnAwpJUHcZoOkwJajBB5aNxTc0I6DcxQBYxAooYYYiYmKLyDTG0GBNQgCFgSkgBYQGCrjIGKogFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}}),
AddedIndex = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
PivotedTable = Table.Pivot(AddedIndex, List.Distinct(AddedIndex[Column1]), "Column1", "Column2", List.Max)
in
PivotedTable
Hope it works
Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!