Having trouble formatting stacked vertical data horizontally

[sander448]

I am pulling data in from a folder containing financials for individual companies. The dates are in the columns and the financial metrics are in the rows. When I pull the data from the folder into power query, it stacks all the data vertically.

I want the end result to look like this with the company name repeated for each year, the the financial metrics horizontally across the top.

I am struggling with formatting this correctly. I am new to using power query.

[Anonymous]

Hi @sander448 ,

You can also refer to the steps below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZC9CsMgEIBfRTIHomfMz9im0K2UruKQwUCHxBJtoW9fz6TFwWYKyHHndxzfnZRZZ8ZHP73JpR91lv/Kg8/xqVxmp94hYlBwVgAFHhcQFyz03/RLT09tA6EYWYhUBNwZ64gZyNrmiUBcCsya0HKejbXkOpvh7vxvjbxCXtbriKT0cSdpHknDP+mKfc0FpKSrFgPymm9Jd/tIt4uz2Dw0oBPHQSVNOuNODV+urdQH", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Column3", "Column4"}),
    Fill = FillRight(#"Replaced Value",{"Column2","Column3","Column4"}),
    #"Transposed Table" = Table.Transpose(Fill),
    Custom1 = Table.Combine(List.Transform({0..2},each Table.FromColumns(List.Range(List.Select(Table.ToColumns(#"Transposed Table"),each _{0}<>null),5*_,5),List.FirstN(Table.ColumnNames(#"Transposed Table"),5)))),
    #"Promoted Headers" = Table.PromoteHeaders(Custom1, [PromoteAllScalars=true]),
    #"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each ([Company Name] <> "Company Name")),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"Date", type date}, {"Revenues", Int64.Type}, {"Cost of Revenue", Int64.Type}, {"Gross Profit", Int64.Type}})
in
    #"Changed Type"

 Fill values to the right in Power Query and Power BI – (thebiccountant.com)

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Omid_Motamedise]

See the next video, 
I have talked about all the possible scenaria in loading data from excel

[Chewdata · Accepted solution]

Hey!

The code below will do exactly what you want. It uses the folder connector to catch all the files in the folder. With the function it will then first 'clean' all the tables, before combining them into one table.

let
    Source = Folder.Files("YOUR FOLDER PATH"),
    #"Added Custom" = Table.AddColumn(Source, "Table", each Excel.Workbook([Content])[Data]{0}),
    fnCleanTable = (vTable as table) =>
    let
        #"Transposed Table" = Table.Transpose(vTable),
        #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
        #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Company name", type text}, {"Date", type text}, {"Reveneus", Int64.Type}, {"Cost of Revenu", Int64.Type}, {"Gross Profit", Int64.Type}}),
        #"Filled Down" = Table.FillDown(#"Changed Type",{"Company name"}),
        #"Changed Type with Locale" = Table.TransformColumnTypes(#"Filled Down", {{"Date", type date}}, "en-US")
    in
        #"Changed Type with Locale",
    invoke_function = Table.AddColumn(#"Added Custom", "CleanTable", each fnCleanTable([Table])),
    #"Removed Other Columns" = Table.SelectColumns(invoke_function,{"CleanTable"}),
    #"Expanded CleanTable" = Table.ExpandTableColumn(#"Removed Other Columns", "CleanTable", {"Company name", "Date", "Reveneus", "Cost of Revenu", "Gross Profit"}, {"Company name", "Date", "Reveneus", "Cost of Revenu", "Gross Profit"})
in
   #"Expanded CleanTable"