Forum Discussion

Justas4478's avatar
Justas4478
Post Prodigy
1 year ago
Solved

Sorting multi header table in power query

Morning, I have folder that reiceves multiple files in it and I will be reading all the files from that folder and combining them. Good thing is that tables in the csv files are all some layout. Th...
Need Help
  • lbendlin's avatar
    lbendlin
    1 year ago

    Create a function that ingests a single file

     

    (f)=>
let
    Source = Csv.Document(f,[Delimiter=",",  Encoding=65001, QuoteStyle=QuoteStyle.None]),
    LZ = List.Skip(List.Transform(List.Zip({Record.ToList(Source{0}),Record.ToList(Source{1})}),each Text.Combine(_,"|")),2),
    #"Removed Bottom Rows" = Table.RemoveLastN(Source,1),
    #"Removed Top Rows" = Table.Skip(#"Removed Bottom Rows",2),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Replaced Headers" = Table.RenameColumns(#"Promoted Headers",List.Zip({List.Skip(Table.ColumnNames(#"Promoted Headers"),2),LZ})),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Replaced Headers", {"Status", "Created Date"}, "Attribute", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByEachDelimiter({"|"}, QuoteStyle.Csv, false), {"Pick Storage", "Attribute"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Value", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Value] <> null) and ([Pick Storage] <> "Totals")),
    #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[Attribute]), "Attribute", "Value"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Pivoted Column",{{"Created Date", type date}},"de")
in
    #"Changed Type1"

     

    Then apply that to the list of files

     

    And finally expand the table.

     

     

     

     

AlienSx's avatar
AlienSx
Super User
1 year ago

replace path_to_folder string with yours

let
    transform_csv = (csv as binary) => 
        [data = Csv.Document(csv,[Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.None]),
        storage = List.Buffer(List.RemoveLastN(List.Alternate(List.Skip(Record.ToList(data{0}), 2), 2, 1), 1)), 
        tx = List.TransformMany(
            Table.ToRows(Table.RemoveLastN(Table.Skip(data, 3), 1)),
            (x) => ((w) => 
                List.Zip(
                    {
                        storage, 
                        List.Alternate(w, 2, 1 , 1),
                        List.Skip(List.Alternate(w, 2, 1, 2)),
                        List.Alternate(w, 2, 1)
                    }
                )
            )(List.RemoveLastN(List.Skip(x, 2), 3)),
        (x, y) => List.FirstN(x, 2) & y
        ),
        z = Table.FromRows(
            List.Select(tx, (x) => x{3} <> ""), 
            {"Status", "Created Date", "Pick Storage", "Orders", "Line Qty", "Pending"})]
        [z],
    result = Table.Combine(List.Transform(Folder.Files("PATH_TO_FOLDER_WITH_FILES")[Content], transform_csv))
in
    result