Forum Discussion
Tables combine
Thanks again,
I put the tables in an Excel file because I couldn't add the PDF invoice.
Indeed the import process is different.
I don't want to charge any pages from the PDF (tables) into the tabs, but rather the result directly.
I was inspired by this video for the process.
I don't understand the language, but it allowed me to get the result that I put in my previous example files.Best regards,
Here is a little different approach.
First, create a custom function to extract the relevant data from your table:
This extracts the relevant column, starting at the location of the header
We will apply the special handling for the "Document" column in the main query.
//rename "fnExtractCol"
(rawTable as table, colHeader as text)=>
[a=Table.ToColumns(rawTable),
b=List.FindText(a,colHeader){0},
col=List.Skip(b,List.PositionOf(b,colHeader)+1)][col]
And the Main Query
Use List.Accumulate and the custom function to process each table and combine them
let
//Changew next lines to create a list of all your tables
//to be used in the List.Accumulate function
Source1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Source2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
Source3 = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
Tbls = List.Accumulate(
{Source1,Source2,Source3},
#table({},{}),
(s,cur)=> Table.Combine({s,
[a=fnExtractCol(cur,"Document No"),
b={List.First(a,2)},
c=fnExtractCol(cur,"Order"),
d=fnExtractCol(cur,"Amount"),
e=Table.FromColumns({b,c,d},
type table[Document No=Int64.Type, Order=text,Amount=Currency.Type]),
f=Table.FillDown(e,{"Document No"})][f]}))
in
TblsFrom your data in your most recent sample file: