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.

     

     

     

     

dufoq3's avatar
dufoq3
Community Champion
1 year ago

Hi Justas4478, another approach:

Replace address to your folder with csv files in Source step.

 

Output

let
    fn_Transform = 
      (tbl as table)=> 
        [
          // _Detail = ContentToTable{0}[Content],
          _Detail = tbl,

          _Helper = [ colNames = Table.ColumnNames(_Detail),
          totalsPos = List.PositionOf(Record.ToList(Table.First(_Detail)), "Totals", Occurrence.All),
          colNamesToRemove = List.Transform(totalsPos, (x)=> colNames{x}) ],
          _RemovedTotalsColumns = Table.RemoveColumns(_Detail, _Helper[colNamesToRemove]),
          _RemovedTotalsRow = Table.SelectRows(_RemovedTotalsColumns, each ([Column1] <> "Totals")),
          _Transposed = Table.FromColumns(Table.ToRows(_RemovedTotalsRow)),
          _MergedHeaders = Table.CombineColumns(_Transposed,{"Column1", "Column2"},Combiner.CombineTextByDelimiter("||", QuoteStyle.None),"Merged"),
          _TransposedBack = Table.PromoteHeaders(Table.FromColumns(Table.ToRows(_MergedHeaders))),
          _Unpivoted = Table.UnpivotOtherColumns(_TransposedBack, {"||", "Pick Storage||Measures"}, "Attribute", "Value"),
          _Splitted = Table.SplitColumn(_Unpivoted, "Attribute", Splitter.SplitTextByDelimiter("||", QuoteStyle.Csv), {"Pick Storage", "Headers"}),
          _FilteredRows = Table.SelectRows(_Splitted, each ([#"||"] = "IM Delivery")),
          _Pivoted = Table.Pivot(_FilteredRows, List.Distinct(_FilteredRows[Headers]), "Headers", "Value"),
          _Renamed = Table.RenameColumns(_Pivoted,{{"||", "Status"}, {"Pick Storage||Measures", "Created Date"}}),
          _ChangedType = Table.TransformColumnTypes(_Renamed,{{"Status", type text}, {"Orders", type number}, {"Line Qty", type number}, {"Pending (IM)", type number}, {"Created Date", type date}}, "sk-SK"),
          _FilteredRows1 = Table.SelectRows(_ChangedType, each not List.ContainsAll({[Orders], [Line Qty], [#"Pending (IM)"]}, {null}) ),
          _SortedRows = Table.Sort(_FilteredRows1,{{"Created Date", Order.Ascending}})
        ][_SortedRows],

    Source = Folder.Files("c:\Downloads\PowerQueryForum\Justas4478\"),
    FilteredCSV = Table.SelectRows(Source, each [Extension] = ".csv"),
    ContentToTable = Table.TransformColumns(FilteredCSV, {{"Content", each Csv.Document(_,[Delimiter=",", Columns=14, Encoding=65001, QuoteStyle=QuoteStyle.None]), type table}}),
    RemovedOtherColumns = Table.SelectColumns(ContentToTable,{"Content", "Name"}),
    Ad_Transformed = Table.AddColumn(RemovedOtherColumns, "Transformed", each Table.AddColumn(fn_Transform([Content]), "SourceName", (x)=> [Name], type text) , type table),
    CombinedFiles = Table.Combine(Ad_Transformed[Transformed])

    
in
    CombinedFiles