Table Transformation

[JonathanJohns]

Good afternoon,

I've got a table like this one :

LOT	Ind 1	Ind 2	Ind 3
1	10
1		50
1			30
2		20
2	20
2			20
3		10
3			10
3	10

And I would like to tansform the table like this one :

LOT	Ind 1	Ind 2	Ind 3
1	10	50	30
2	20	20	20
3	10	10	10

Do you have any idea how to make that please ? My example is only with 3 lines and 3 columns but in reality I have 400 columns and 10 000 lines.

Thank you for your help.

[Interkoubess · Accepted solution]

Hi @JonathanJohns,

You can use these codes below in Power Query. To sum up please unpivot your data first and pivot it.

Code 1 : I used the url you gave:

let
    Source = Web.Page(Web.Contents("http://community.powerbi.com/t5/Desktop/Table-Transformation/m-p/404994#M185333")),
    Data0 = Source{0}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Data0, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"LOT", Int64.Type}, {"Ind 1", Int64.Type}, {"Ind 2", Int64.Type}, {"Ind 3", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"LOT"}, "Attribute", "Value"),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[Attribute]), "Attribute", "Value")
in
    #"Pivoted Column"

Code 2: In your data, you can accomplish these steps

 #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"LOT"}, "Attribute", "Value"),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[Attribute]), "Attribute", "Value")

Let us know if you did not succeed in doing it...

Hope it helps.

Ninter

[JonathanJohns]

It works.

Thank you !