Excel Power Query - Split invoicing data table at the row before last instance of a header row

[Wishee]

Hi there,

I have a PQ output with vendor invoicing data in the form of one header and a variable number of transactions. This data needs to be uploaded to SAP where the max no of rows that can be uploaded in one file is 999.

I need to split the table into multiple tables where the SAP limit is observed and each table includes the full billing details for each Vendor (header row 1 plus a variable no of transactions on each invoice).

In my screenshot the last vendor before row 999 is on data row 920 (col.T) so I want data rows 2 to 919 to be split into a table called Upload 1.

Subsequently I want to continue down so that Upload 2 has row 920 as the first data row and row 1,914 will be the first data row for Upload 3.

I have been manually splitting the file in Excel but I would love to have solution in PQ or alternatively a dynnamic formula in col.Y but I'm struggling to to furmulate a function using COUNTIFS and INDIRECT.


I hope you can help please.

William

 

[AlienSx]

Hi, William
PQ definitely can calculate upload numbers. However single query won't generate many objects (tables in your case). As a workaround I would suggest you to transform your table to generate upload number column (see code below), manually create reasonable amount of queries with a reference to your query and filter one upload number per query. Then you will be able to load them into separate Excel tabs for further processing. Can't help you with Excel formula but I am pretty sure it is doable.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    // max number of rows in the batch
    max_vol = 99,
    // group by vendor to calculate rows
    group = Table.Group(
        Source,
        "Vendor Number",
        {{"x", (x) => x}, {"count", Table.RowCount}},
        GroupKind.Local,
        (s, c) => Number.From(c <> null)
    ),
    // generate a record with vendor as field name and "Upload No" as value
    lst = List.Buffer(List.Zip({group[Vendor Number], group[count]})),
    updates = List.Generate(
        () => [i = 0, lim = 1, s = lst{0}{1}, update = 1],
        (x) => x[i] < List.Count(lst),
        (x) =>
            [
                i = x[i] + 1,
                lim = Number.From((x[s] + lst{i}{1}) <= max_vol),
                s = lst{i}{1} + x[s] * lim,
                update = x[update] + 1 - lim
            ],
        (x) => [Name = Text.From(lst{x[i]}{0}), Value = "Update " & Text.From(x[update])]
    ),
    rec = Record.FromTable(Table.FromRecords(updates)),
    // add Upload Number column to your table
    upl_no = Table.AddColumn(group, "Upload Number", (x) => Record.Field(rec, Text.From(x[Vendor Number]))),
    // expand transactions - take care of the list of columns
    expand = Table.ExpandTableColumn(upl_no, "x", {"Item Text", "Row No", "Transaction Index"})
in
    expand