Table Transforming

[Mat87]

So I have a table that looks like this:

I want to transformat it into something that looks like this:

Any idea what would be the way to do this in DAX?

[Mat87]

Thanks guys. 

[v-huizhn-msft · Accepted solution]

Hi @Mat87,

If you allow, you can edit the table in Query Edit.

1. Please select Year column->Pivot Column.



2. After click Pivot Column->value field.



3. Then alick close&Apply, you will get theexpected result.



In addition, this is the Power Query statement.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTIyMDQGUcamJkZKsTpwQRMQZWJkAhZLAosZWILFDI2QxSyAlKmpEUgwFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, Year = _t, value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Year", Int64.Type}, {"value", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type", {{"Year", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Changed Type", {{"Year", type text}}, "en-US")[Year]), "Year", "value", List.Sum)
in
    #"Pivoted Column"

Please feel free to ask if you have any issue.

Best Regards,
Angelia

[Anonymous]

@Mat87

I assume you ask how display the table in the desired format and not to change the table itself.

You can use the Matrix.