Forum Discussion

sander448's avatar
sander448
New Member
1 year ago
Solved

Having trouble formatting stacked vertical data horizontally

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 po...
Need Help
  • Chewdata's avatar
    Chewdata
    1 year ago

    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"
Anonymous's avatar
Anonymous
Not applicable
1 year ago

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