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,
Hi Mederic
I would use below strategies if in your situation.
1. Create one reusable Function for data cleaning as below and apply this function on each table
(table) =>
let
Source = table,
HeaderRow = List.First(List.Select(Table.ToRows(Source), each List.Contains(_, "Document No"))),
HeaderRowIndex = List.PositionOf(Table.ToRows(Source), HeaderRow),
PromoteHeaders = Table.PromoteHeaders(Table.Skip(Source, HeaderRowIndex)),
RemoveNulls = Table.SelectRows(PromoteHeaders, each [Document No] <> null and [Document No] <> ""),
SelectColumns = Table.SelectColumns(RemoveNulls, {"Document No", "Order", "Amount"}, MissingField.UseNull)
in
SelectColumns
2. Use Table.Combine or 'Append Queries as New' from UI to combine all your tables.
3. Add a custom column that returns the Document No only for rows where it's a number, and blanks for name rows
4. Fill down the Document No so each order row gets matched with the correct document.
5. Remove rows where both Order and Amount are blank.
6. Move the No column to the left as your new Document No column. Optionally, you can also remove the original Document No column if it’s no longer needed.
You can also wrap Steps 2–5 into another function if you're repeating this logic across multiple datasets.
In case you would like to refer to this file, please use this link
Hope this helps:)
Thank you for your replies,
Masson, thank you for your clear explanations and this solution.
But Ibendlin's comment made me realise that my file was not exactly the same as the one in my first message.
And sorry for the mistake.
Here is a new file and screenshots of what I would like to do in a specific step, namely adding the column number to the location shown in the screenshot.
Thanks in advance
Best regards
If you need one solution ready for use on your file, please share some real sample data from each table.
Also on your picture, what does it mean by making 'Invoice No.' dynamic? I'm not sure i understand your logic.
Thank MasonMA you for your reply.
With "make the column dynamic", I meant "automatic" . As we can see, I added a hard-coded formula.
Regarding a real file:
- the source comes from a .pdf file with tables and pages.
- Each table in my example file corresponds to the pages of the .pdf file.
- Each page contains 10 to 11 columns and approxi. 70 rows.
- I have between 5 and 10 pages per invoice.
I want to combine the pages of a single invoice at a time, not of multiple invoices
I have managed to achieve what I wanted, but I am stuck on the "Invoice No" ("Document No") column because, This information is sometimes in column 2 or 3.
In my fictitious file, I have placed "Invoice No" in column 2 or 4 for illustration purposes.I hope that was clear.
Thank you in advance.Best regards
Hello Mederic
I was having the same challenge handling your Document No. when i was playing with your mock file, that's why i created one additional column for these Document No. and use this new Column as a replacement of your old Document No. column (If you see my Step 3 and 4), then filling them down so that right Document No. can be attached to the right Orders.
In my proposal, if you identified that Document No. only appear on Column 2 and 3, you may just need to update AddColumn by creating an inner block (of course you can further tweak the code and make it even more dynamic if you are not sure which Columns have Document No.)
AddInvoiceNoColumn = Table.AddColumn(Source, "Document No", each let colsToCheck = { [Column2], [Column3]}, foundDocNo = List.First( List.Select(colsToCheck, each Value.Is(Value.FromText(Text.Trim(_)), Int64.Type)), null ) in foundDocNo )Hope this gives you some ideas:)