Let’s say you have a set of Excel files and you want to feed that information into one Excel file, to allow you to do summaries or produce Charts. How would you go about it? Here is one way:
Install Power Query from:
https://www.microsoft.com/en-us/download/details.aspx?id=39379&CorrelationId=85f847dd-369e-4417-b604-6a2f3c673084
Use this function (copied from link below) to import multiple XLSX to one sheet.
//Define function parameters
(#"Directory containing Excel files to combine" as text,
optional #"Name of each Excel object to combine" as text,
optional #"Use first rows as headers" as logical) =>
let
//If the optional Excel object name parameter is not set, then default to Sheet1
ExcelName = if #"Name of each Excel object to combine" = null
then "Sheet1"
else #"Name of each Excel object to combine",
//If the optional Use first rows as headers parameter is not set, then default to true
UseFirstRowsAsHeaders = if #"Use first rows as headers"= null
then true
else #"Use first rows as headers",
//Get a list of all the files in the folder specified
Source = Folder.Files(#"Directory containing Excel files to combine"),
//Filter these to only get Excel files
OnlyGetExcelFiles = Table.SelectRows(Source,
each ([Extension] = ".xlsx")
or ([Extension] = ".xls")),
//Find the full path of each file
FullPath = Table.CombineColumns(
OnlyGetExcelFiles ,
{"Folder Path", "Name"},
Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
//Get a list containing each file path
ExcelFiles = Table.Column(FullPath, "Merged"),
//Define a function to get the data from the specified name in each Excel workbook
GetExcelContents = (FileName as text) =>
let
//Connect to the workbook
Source = Excel.Workbook(File.Contents(FileName), UseFirstRowsAsHeaders),
//Get a table of data from the name specified
//If the name doesn't exist catch the error and return null
ExcelData = try Source{[Item=ExcelName]}[Data]
otherwise try Source{[Name=ExcelName]}[Data]
otherwise null
in
ExcelData,
//Call the above function for each Excel file
ReadAllWorkbooks = List.Transform(ExcelFiles, each GetExcelContents(_)),
//Remove any null values resulting from errors
IgnoreNulls = List.RemoveNulls(ReadAllWorkbooks),
//Combine the data from each workbook into a single table
CombineData = Table.Combine(IgnoreNulls)
in
CombineData
Here are some of the steps to get the function into Excel:
Read more at:
Combining Data From Multiple Excel Workbooks With Power Query–The Easy Way! | Chris Webb’s BI Blog
