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,

ronrsnfld's avatar
ronrsnfld
Super User
1 year ago

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
    Tbls

From your data in your most recent sample file:

 

 

Mederic's avatar
Mederic
Post Patron
1 year ago

Good evening everyone,
I have carefully reviewed the various solutions.
It seems to me that we are not using the same data model (start of Data).
And it is not easy to explain when the source is a .pdf file. So please accept my apologies.
I have recreated a new file here that is representative of my personal file.
I hope this will be much clearer.
Thank you in advance.

Best regards,

  • ronrsnfld's avatar
    ronrsnfld
    Super User
    1 year ago

    The data that I was using is the data that YOU supplied (your Excel file that you linked to).

     

    Your latest Excel sheet is slightly different from what you have previously supplied. And only minor changes are required in the code I supplied for it to produce your desired result (on your Result tab) given your six tables on your Template Invoice 536 tab.

     

    So I don't really understand your issue.

     

    The relevant differences between the data YOU supplied this time and the data YOU supplied previously has to do with the extra empty cells in the Order and Amount Columns, as well as the different capitalization of Document No vs Document no; both issues easily taken care of with minor code alterations.

    Also, one of your tables has no data for Order and Amount. so we need to check for the error and return an empty table if that is the case.

     

    let

//Changew next lines to create a list of all your tables
//to be used in the List.Accumulate function

    Source1 = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content],
    Source2 = Excel.CurrentWorkbook(){[Name="Tableau2"]}[Content],
    Source3 = Excel.CurrentWorkbook(){[Name="Tableau3"]}[Content],
    Source4 = Excel.CurrentWorkbook(){[Name="Tableau4"]}[Content],
    Source5 = Excel.CurrentWorkbook(){[Name="Tableau5"]}[Content],
    Source6 = Excel.CurrentWorkbook(){[Name="Tableau6"]}[Content],
    
    Tbls = List.Accumulate(
                {Source1,Source2,Source3,Source4,Source5, Source6},
                #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=try Table.FillDown(e,{"Document No"}) otherwise #table({},{})  ][f]}))                   

in
    Tbls

      

    Of course, I also wonder about the process you use to get the PDF files into the six tables that you show, and wonder if there might be some improvement at that level.

    • Mederic's avatar
      Mederic
      Post Patron
      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,