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

[MichaelJ64]

Hi community,
Newbie here. I am struggling to create an M code equivalent for the following action:

     Foreach non-empty cell in one column table,

          expand the cell contents (it's an Excel worksheet)  // M function Table.ExpandTableColumn

          extract 8 values from the worksheet from known cell locations  

          append these 8 values to the table created for them   //Table.InsertRows (AdviceRequest, Table.RowCount(AdviceRequest),{...} 

    end 

To elaborate, I have a standard query form as an Excel workbook with three sheets.  The pre-set elements in the first sheet are filled out and e-mailed to a central account.  I want to harvest 8 of the pre-set elements and put them in a table, one row for each e-mail.  I don't know how many e-mails I have, and it changes.

I can get the e-mails no problem and the correct sheet of the Excel Workbook (pre-defined and stable).  I also know how to extract the eight elements I want (always the same cells).  I have the code to create a table for these 8 elements #"AdviceRequest".

I get as far as a one-column table called "Removed Errors1" with column labelled "Transform File" where each element contains a table with the worksheet from each e-mail in the rows (4 e-mails, 4 rows; 12 e-mails, 12 rows; ...) .  I used Table.ExpandTableColumn so I could test my code to get the eight elements I want (7 in column three, 1 in column 5) and it works no problem.

What I want to do is select my eight elements in the middle of the Table.ExpandTableColumn command.  I don't see how to get this to work.  I read about using Table.AddColumn to loop over the elements of #"Removed Errors1"[Transform File] and wrote a function, to expand the 'table' sent to it, extract the 8 values from the resulting (Excel) table, and append to the pre-defined table. 

This clearly can't work because I can't use a function to change something else, only to return a result.  I've not found a question sufficiently similar for my rather limited skill to use.

I would welcome some help on this. 

Thanks,

MichaelJ64 

[lbendlin · Accepted solution]

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.

[lbendlin · Accepted solution]

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.

[MichaelJ64 · Accepted solution]

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