Forum Discussion

Mat87's avatar
Mat87
Frequent Visitor
9 years ago
Solved

Table Transforming

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?

Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    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

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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.

     

     

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Microsoft Employee
    9 years ago

    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