How to transpose data twice

[Anonymous]

Can you please help me with transposing the following data in PowerBI

CURRENT STRUCTURE

Company	Sales	Sales	Sales	Profit	Profit	Profit
	31/12/2010	31/12/2011	31/12/2012	31/12/2010	31/12/2011	31/12/2012
A	100	125	150	25	50	75
B	200	225	250	60	80	120
C	300	350	400	140	160	180

DESIRED STRUCTURE

Company	Date	Attribute	Amount
A	31/12/2010	Sales	100
A	31/12/2011	Sales	125
A	31/12/2012	Sales	150
A	31/12/2010	Profit	25
A	31/12/2011	Profit	50
A	31/12/2012	Profit	75
B	31/12/2010	Sales	200
B	31/12/2011	Sales	225
B	31/12/2012	Sales	250
B	31/12/2010	Profit	60
B	31/12/2011	Profit	80
B	31/12/2012	Profit	120

[smpa01 · Accepted solution]

@Anonymous  you can try this

let
    Source = Web.BrowserContents("https://community.powerbi.com/t5/Desktop/How-to-transpose-data-twice/m-p/949463#M454935"),
    #"Extracted Table From Html" = Html.Table(Source, {{"Column1", "DIV[id='bodyDisplay'] > DIV.lia-message-body-content:nth-child(1) > TABLE:nth-child(5) > TR > :nth-child(1), DIV[id='bodyDisplay'] > DIV.lia-message-body-content:nth-child(1) > TABLE:nth-child(5) > * > TR > :nth-child(1)"}, {"Column2", "DIV[id='bodyDisplay'] > DIV.lia-message-body-content:nth-child(1) > TABLE:nth-child(5) > TR > :nth-child(2), DIV[id='bodyDisplay'] > DIV.lia-message-body-content:nth-child(1) > TABLE:nth-child(5) > * > TR > :nth-child(2)"}, {"Column3", "DIV[id='bodyDisplay'] > DIV.lia-message-body-content:nth-child(1) > TABLE:nth-child(5) > TR > :nth-child(3), DIV[id='bodyDisplay'] > DIV.lia-message-body-content:nth-child(1) > TABLE:nth-child(5) > * > TR > :nth-child(3)"}, {"Column4", "DIV[id='bodyDisplay'] > DIV.lia-message-body-content:nth-child(1) > TABLE:nth-child(5) > TR > :nth-child(4), DIV[id='bodyDisplay'] > DIV.lia-message-body-content:nth-child(1) > TABLE:nth-child(5) > * > TR > :nth-child(4)"}, {"Column5", "DIV[id='bodyDisplay'] > DIV.lia-message-body-content:nth-child(1) > TABLE:nth-child(5) > TR > :nth-child(5), DIV[id='bodyDisplay'] > DIV.lia-message-body-content:nth-child(1) > TABLE:nth-child(5) > * > TR > :nth-child(5)"}, {"Column6", "DIV[id='bodyDisplay'] > DIV.lia-message-body-content:nth-child(1) > TABLE:nth-child(5) > TR > :nth-child(6), DIV[id='bodyDisplay'] > DIV.lia-message-body-content:nth-child(1) > TABLE:nth-child(5) > * > TR > :nth-child(6)"}, {"Column7", "DIV[id='bodyDisplay'] > DIV.lia-message-body-content:nth-child(1) > TABLE:nth-child(5) > TR > :nth-child(7), DIV[id='bodyDisplay'] > DIV.lia-message-body-content:nth-child(1) > TABLE:nth-child(5) > * > TR > :nth-child(7)"}}, [RowSelector="DIV[id='bodyDisplay'] > DIV.lia-message-body-content:nth-child(1) > TABLE:nth-child(5) > TR, DIV[id='bodyDisplay'] > DIV.lia-message-body-content:nth-child(1) > TABLE:nth-child(5) > * > TR"]),
    #"Changed Type" = Table.TransformColumnTypes(#"Extracted Table From Html",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    not0not1 = Table.SelectRows(#"Added Index", each ([Index] <> 0 and [Index] <> 1)),
    #"0" = Table.SelectRows(#"Added Index", each ([Index] =0)),
    #"1" = Table.SelectRows(#"Added Index", each ([Index] =1)),
    Custom1 = #"1"&not0not1,
    #"Promoted Headers" = Table.PromoteHeaders(Custom1, [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"", type text}, {"31/12/2010", Int64.Type}, {"31/12/2011", Int64.Type}, {"31/12/2012", Int64.Type}, {"31/12/2010_1", Int64.Type}, {"31/12/2011_2", Int64.Type}, {"31/12/2012_3", Int64.Type}, {"1", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"1"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {""}, "Attribute", "Value"),
    #"Added Index1" = Table.AddIndexColumn(#"Unpivoted Other Columns", "Index", 1, 1),
    Custom2 = #"0"&not0not1,
    #"Promoted Headers1" = Table.PromoteHeaders(Custom2, [PromoteAllScalars=true]),
    #"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers1",{{"Company", type text}, {"Sales", Int64.Type}, {"Sales_1", Int64.Type}, {"Sales_2", Int64.Type}, {"Profit", Int64.Type}, {"Profit_3", Int64.Type}, {"Profit_4", Int64.Type}, {"0", Int64.Type}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type2",{"0"}),
    #"Unpivoted Other Columns1" = Table.UnpivotOtherColumns(#"Removed Columns1", {"Company"}, "Attribute", "Value"),
    #"Added Index2" = Table.AddIndexColumn(#"Unpivoted Other Columns1", "Index", 1, 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1", {"Index"}, #"Added Index2", {"Index"}, "Added Index2", JoinKind.LeftOuter),
    #"Expanded Added Index2" = Table.ExpandTableColumn(#"Merged Queries", "Added Index2", {"Attribute", "Value"}, {"Attribute.1", "Value.1"}),
    #"Removed Columns2" = Table.RemoveColumns(#"Expanded Added Index2",{"Index"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns2", "Attribute", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Attribute.1.1", "Attribute.2"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1.1", type text}, {"Attribute.2", Int64.Type}}),
    #"Removed Columns3" = Table.RemoveColumns(#"Changed Type3",{"Attribute.2"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Removed Columns3", "Attribute.1", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Attribute.1.1.1", "Attribute.1.2"}),
    #"Changed Type4" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Attribute.1.1.1", type text}, {"Attribute.1.2", Int64.Type}}),
    #"Removed Columns4" = Table.RemoveColumns(#"Changed Type4",{"Attribute.1.2"})
in
    #"Removed Columns4"

[Anonymous]

smpa's solution is essentially what I was going to suggest. To enter the M code, click the view tab in the power query editor and click "Advanced Editor". This is really the only way I can think of doing this without modifying the data pre pbi.

[Anonymous]

@Anonymous 

My solution to this issue is selecting the Company column then "unpivot other columns". This will give you something like this:

Then you split the "Value" column before the first sales amount and duplicate the dates in a new column. 

[Anonymous]

Thanks for the response, hreid.

Could you please explain what is meant by splitting the column and duplicating the dates. 

I am new to powerbi and this might sound silly. 🙂

[Anonymous]

Well, the dates in the same column as prices are what causes most of the complexity. Is it possible to create a new column with just the dates in your dataset (not in pbi)? If it is, then you can follow the same steps and be done. If your dataset cannot be altered pre-powerbi its a little more challenging.