Excel - Powerquery : From folder : Multiple files with unfixed starting row

Question

Please refer image link above

I have multiple files xlsx in one folder . Each file has only one sheet.( Sample 4 source files are shown in image) with file name.

Each file have top n rows as transaction data ( unfixed n rows between 10-100 rows).

In the bottom there is a summary of transactions.( unfixed first row. unfixed items )

I am looking for only a summary of all items with file name using powerquery.

Problem : My summary rows sometimes stars from 10 sometimes 32, sometimes 100. it is not fixed hence unable to code.

Olly Olly · Accepted Answer · 2019-02-18T09:20:18

This will look for the data below the "Total Summary" row, for each file, then combine that data from each workbook:

Query: fnWeeklySummary

(Workbook, Week) =>
let
    Source = Excel.Workbook(Workbook, null, true),
    Worksheet = Source{0}[Data],
    #"Added Index" = Table.AddIndexColumn(Worksheet, "Index", 1, 1),
    #"Rows To Skip" = Table.SelectRows(#"Added Index", each Text.Contains([Column1], "Total Summary"))[Index]{0},
    #"Skipped Rows" = Table.Skip(Worksheet,#"Rows To Skip"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Skipped Rows", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"SKU", type text}, {"Inventory", Int64.Type}, {"Sellin", Int64.Type}, {"Ratio", Percentage.Type}}),
    #"Added Week" = Table.AddColumn(#"Changed Type", "Week", each Week, type text),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Week",{"Week", "SKU", "Inventory", "Sellin", "Ratio"})
in
    #"Reordered Columns"

Query: Weekly Data

let
    Source = Folder.Files("D:\WeeklyData"),
    #"Invoked Custom Function" = Table.AddColumn(Source, "Summary Data", each fnWeeklySummary([Content], Text.Replace([Name], ".xlsx", ""))),
    #"Combined Summary Data" = Table.Combine(#"Invoked Custom Function"[Summary Data])
in
    #"Combined Summary Data"

Excel - Powerquery : From folder : Multiple files with unfixed starting row

1 Answers