Forum Discussion

Mederic's avatar
Mederic
Post Patron
1 year ago
Solved

Tables combine

Hello everyone, I would like to combine several tables from the same file and keep only certain columns. The problem is that the column headings are not always in the same place. I have attached a...
Need Help
  • Mederic's avatar
    Mederic
    1 year ago

    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,

MasonMA's avatar
MasonMA
Super User
1 year ago

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.

Mederic's avatar
Mederic
Post Patron
1 year ago

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

  • MasonMA's avatar
    MasonMA
    Super User
    1 year ago

    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:)