Arranging panel data

[Bootje]

Hey community!

I would really appreciate some help with an excel problem that I'm having. I attached an excel file for clarification.

I want to transform a file that has the same layout as the example file but with thousands of lines into panel data. Meaning, the top table needs to be tranformed into the bottom table as efficient as possible as control + c'ing thousands of lines would take too much time. 

How can I perform this task as efficient as possible?

Thanks in advance!

[#2]

Hi @Bootje;

You can use Power Query for this transformation

1. Convert your Raw Data in Tables & then load it into Power Query Editor

2. When you convert it into tables uncheck my table has headers

Below are the step from the Power Query for the transformation. I will be attaching the solution file for your reference. 

let
    Source = Excel.CurrentWorkbook(){[Name="RawData"]}[Content],
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"date"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Attribute.2"}),
    #"Added Conditional Column" = Table.AddColumn(#"Removed Columns", "company id", each if Text.StartsWith([Attribute.1], "company") then [Value] else null),
    #"Filled Down" = Table.FillDown(#"Added Conditional Column",{"company id"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each not Text.StartsWith([Attribute.1], "company")),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"date", type date}, {"Attribute.1", type text}, {"Value", Currency.Type}, {"company id", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Attribute.1]), "Attribute.1", "Value", List.Sum),
    #"Sorted Rows" = Table.Sort(#"Pivoted Column",{{"company id", Order.Ascending}, {"date", Order.Ascending}})
in
    #"Sorted Rows"

Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert

If you find the above solution resolved your query don't forget mark as Official/Best Answer to help the other members find it more