Parsed Filename as Column when connecting to a folder containing multiple Excel files

[skiotterjeffa]

I have a folder with several Excel files:

Earnings Report June 2018.xlsx

Earnings Report May 2018.xlsx

Earnings Report April 2018.xlsx

etc.

I need to combine them in in Power BI, but I want to retain the commission period month that is found in the filename and display that month as a calculated column in Power BI.

I have been able to calculate the commission period on the Source step in a calculated colum.

I have found many examples of how to do this with CSV files, but I don't have an option for getting a CSV file without opening in Excel and saving as a new file format, which we are trying to avoid.

What is tripping me up is how to drill down into the specific sheet I want in the Excel workbook. With the CSV file, the data is on the first 'Table' when you expand the Binary. In an Excel file, the first Table is a listing of the Sheets when you drill into the binary. Then you can drill into a second table and see the data.

With CSV, you can do a Table.AddColumn and loop through all Csv.Document, even including a filter for your column separator, etc.

If I am just dealing with one Excel file and navigate into a Sheet, this code is created:

= #"Imported Excel"{[Item="Earning Summary",Kind="Sheet"]}[Data]

I cannot figure out the syntax in Power BI to wrap that in an each loop, though.

Thank You

Jeff

[v-yulgu-msft]

Hi @skiotterjeffa,

You can add a custom column:

=Text.Range(Text.Range([Name],16),0,(Text.Length(Text.Range([Name],16))-10))

Best regards,

Yuliana Gu

[TomMartens]

Hey,

maybe the content of your folder looks something like this:

you can use the "Extensions" column to filter down to the xlsx files that you wand to process.

Then you can use the PowerQuery function "Excel.Content" to create a custom column like so:

Excel.Workbook([Content])

This returns a table object for each xlsx file.

If you expand the column you will see this

The Data column contains data from the workbook, all the other columns contain additional meta information about the content of "Data".

Hopefully this gets you started.

Regards,

Tom

[skiotterjeffa]

Tom, That method did retain the filename in a column, yes.

I'm unable to transform the data, however.  The data needs to have the first row of each Excel file promoted to a header. It will also have the first 14 rows removed.

I tried updating the formula for my new expanded column on data with

= Table.PromoteHeaders(Table.ExpandTableColumn(#"Added Custom", "Custom", {"Name", "Data"}, {"Custom.Name", "Custom.Data"}), [PromoteAllScalars=true]).

But that isn't showing the column names promoted in the preview.

I also tried doing the PromoteHeaders on the AddedColum step where the each loop was. When I did that, my expanded Custom.Data column no longer showed the table.

Thank you for your help.

[v-yulgu-msft]

Hi @skiotterjeffa,

"I'm unable to transform the data, however.  The data needs to have the first row of each Excel file promoted to a header. It will also have the first 14 rows removed."

Could you post some dample data to illustrate your scenario?

Regards,

Yuliana Gu