Expand column containing Excel workbooks, extracting 8 values from each workbook

What you want to do is define a custom function that takes the link to an excel file as its parameter and then returns a table with the relevant data from that Excel file.

In your main M script you then call that function via AddColumn, and in the next step you can then append the resulting tables.

Hi lbendln,

Thanks for your pointer.  I got it to work from your tip.  I should not try to create a separate table, rather I should (sensibly)

expand my table to have the columns I want.  That is, the columns for each of my 8 parameters are expanded as columns beside the column(s) holding my extracted Excel sheet. 

My code is messy and inefficient, but I will have at most hundreds of entries so that is acceptable to me.  I extract my Excel Worksheet and keep both the table generated by it and the xls binary.  Pretty sure there is a more elegant solution, but this works (so far).  I have copied my code below for anyone interested.   It is peppered with comments to help me know what is going on.  Note "Mymailbox" is a place holder for my actual mail account 🙂

let

    Source = Exchange.Contents(Mymailbox),      // mail address

    Mail1 = Source{[Name="Mail"]}[Data],

    #"Filtered Rows" = Table.SelectRows(Mail1, each ([Folder Path] = "\Test\")),  // only retrieve mail from 'Test'

    #"Expanded Attachments" = Table.ExpandTableColumn(#"Filtered Rows", "Attachments",

                              {"AttachmentContent"}, {"Attachments.AttachmentContent"}),

/*  Note that this will generally create 3 rows per e-mail - the Excel attachment, another row

      for the e-mail header (To:, From:, Date:, and so on), and a third row for the signature block */

    #"Filtered Hidden Files1" = Table.SelectRows(#"Expanded Attachments",

                               each [Attributes]?[Hidden]? <> true),        // clean up, remove entries with no attachments

    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each

              #"Transform File"([Attachments.AttachmentContent])),   

/* #"Transform file" is a system generated function created using the 'expand columns' icon and choosing "Sheet1"

      as the sheet we want to use.  It uses the Excel.Workbook command to get the names of the sheets in the

      workbook, and then returns Sheet1 in the column "Attachments.AttachmentContent"  */

    #"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1",

                                           {"Attachments.AttachmentContent", "Transform File"}),

/*  This removes the columns we don't want.  The system generated code limited this to the single column

      "Transform File" containing 'Sheet1' of the xls file as a table.  I actually want the binary xls sheet so my custom

       function can expand the table and select the 8 cells I want to get from it.  This is in the other column */

    #"Removed Errors1" = Table.RemoveRowsWithErrors(#"Removed Other Columns1",

                                               {"Transform File"}),

/* Each e-mail had 3 attachments.  The mail header and signature attachments generate errors because they

     don't contain an Excel workbook.  This command removes these unwanted rows, creating a table with one

     row for each e-mail.   */

     ExtractXLS = Table.AddColumn(#"Removed Errors1", "NewRowQueryElements", each

                    ExtractQueryToTable([Attachments.AttachmentContent] ) ),

    #"Expanded NewRowQueryElements" = Table.ExpandTableColumn(ExtractXLS, "NewRowQueryElements",

                    {"Title", "LGA", "Section", "Priority", "ProjType", "DateRec", "DateDue", "DateStat"} ),

/* Not quite what I expected - ExtractQueryToAdviceTable is my function to extract the 8 parameters

   from the Excel spreadsheet (Sheet1).  It returns a record holding the 8 parameters.  Tried to send that to

  'InsertNewRow' to populate table "AdviceRequest", but that's not how the declarative program works.

  (Of course) the record generated is put in to the new column, so I called it "NewRowQueryElements"   */

    #"Changed Type" = Table.TransformColumnTypes(#"Expanded NewRowQueryElements",{

                      {"DateRec", type date},

                      {"DateDue", type date},

                      {"DateStat", type date}  }    ),

    #"RemoveInterimColumns" = Table.SelectColumns(#"Changed Type", {"Title", "LGA", "Section",

                                      "Priority", "ProjType", "DateRec", "DateDue", "DateStat"} )

in

    #"RemoveInterimColumns"

+++

FUNCTION EXTRACTQUERYTOTABLE

= ( RequestAttach as binary ) => let

   Workbook = Excel.Workbook (RequestAttach, null, true ),

   RequestSheet = Workbook{[Item="Sheet1", Kind="Sheet"]}[Data],

   #"Changed Type" = Table.TransformColumnTypes(#"RequestSheet",

                          {{"Column1", type any}, {"Column2", type text}, {"Column3", type any},

                              {"Column4", type text}, {"Column5", type any}, {"Column6", type any},

                              {"Column7", type text}, {"Column8", type text}, {"Column9", type text},

                              {"Column10", type text}, {"Column11", type text}, {"Column12", type text},

                              {"Column13", type any}}),

   #"ThisTitle" = #"Changed Type"{3}[Column3],

   #"ThisLGA" = #"Changed Type"{6}[Column3],

   #"ThisSection" = #"Changed Type"{7}[Column3],

   #"ThisPriority" = #"Changed Type"{20}[Column3],

   #"ThisProjType" = #"Changed Type"{21}[Column3],

   #"ThisDateRec" = #"Changed Type"{22}[Column3],

   #"ThisDateDue" = #"Changed Type"{23}[Column3],

   #"ThisDateStat" = #"Changed Type"{23}[Column5],

   #"NewRow" = { [Title=#"ThisTitle", LGA=#"ThisLGA", Section=#"ThisSection", Priority=#"ThisPriority",

               ProjType=#"ThisProjType", DateRec=#"ThisDateRec", DateDue=#"ThisDateDue",

              DateStat=#"ThisDateStat" ]  }

in

    #"NewRow"

+++

FUNCTION TRANSFORM FILE

= (Parameter1 as binary) => let

        Source = Excel.Workbook(Parameter1, null, true),

        Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data]

    in

        Sheet1_Sheet