Looping though tabs or tables to extract data from excel files

[Anonymous]

I am extracting data from excel files using PowerQuery M and I want to loop through tabs of the spreadsheet, but can't find out how to execute loops in M. Does anyone know the syntax? The pseudocode would look something like:

// define a table variable called AllTabs to hold the combined table
AllTabs = let
    for( count = 1; 6; count++ )
    SourceTab = concatenate( "Case", count )
    // open the SourceTab and do something useful with the tab contents
    Table.Combine( AllTabs, SourceTab )
in AllTabs

This code would loop though 6 tabs and perform the same transformation operations on the contents of each tab, and then append the resulting SourceTab table to the AllTabs table

Thanks for any help on this!

[Anonymous · Accepted solution]

Thanks for your replies. I reframed the problem and came up with a function that solves the problem better in a different way. The function looks in a specified folder and subfolders (sourcePath) for all excel files and then returns a table with all the tabs for all the files based on an optional file name filter (fileFilter) passed to the function. The resulting table can then be processed manually or passed to another function that adds a column with a table in each row that is the transformed tab data for that row.

One advantage of this function is that it returns all the file and tab metadata for audit or filtering purposes later. I thought it might be something that others might want to use when extracting data from excel files. I've included the code below:

GetExcelTabData = ( sourcePath as text, optional fileFilter as text ) =>
   let

   // get a table of all the excel files in the sourcePath
   sourceTable = Table.SelectRows(
      Folder.Files( sourcePath ), each Text.Contains( [Extension], "xls" )
   ),

   // if there is no optional fileFilter specified then show all the files in the folder
   fileTable = if fileFilter is null then sourceTable
      else Table.SelectRows( sourceTable, each Text.Contains( [Name], fileFilter ) ),

   addTabColumn = Table.AddColumn( fileTable, "tab_data", each Excel.Workbook( [Content] ) ),

   // get the list of column names to show in the final table, all but the "Name" column which is redundant with "Item"
   colNames = List.RemoveMatchingItems( Table.ColumnNames( addTabColumn[tab_data]{0} ), { "Name" } ),

   // expand the tab column so that all the tabs and tab metadata show in the final rsult table
   expandTabColumn = Table.ExpandTableColumn( addTabColumn, "tab_data", colNames ),

   result = expandTabColumn
in
   result

[Aron_Moore]

I've used this method with great success: https://potyarkin.ml/posts/2017/loops-in-power-query-m-language/ 

[ChrisMendoza]

@Anonymous - Have you taken a look at https://www.youtube.com/watch?v=KfuYxBDBkAo? It seems similar to your need.