Combine Excel files and add a column identifying the employee

[Anonymous]

I need to combine about 40 timesheets into one table using Power Query.  Each worksheet contains the employee name in Cell A1.  How do I set up a transform that will pull the Employee name and populate a new Column A for each sheet I append?

Also, when I specify the Excel table as the object to bring into the query, I only want the non-blank rows but I'm getting all the rows.  How do I eliminate the blank rows from the result table?

I'd gladly append examples but I don't see a control for attachments as in other Communities.

[v-jingzhang · Accepted solution]

Hi @Anonymous 

Suppose your raw data is like below in Excel, you can pull the employee name and populate a new column by adding a custom column.

= Record.Field(#"Previous Step Name"{0}[[Column1]],"Column1")

Or add a step

= Table.AddColumn(#"Previous Step Name", "Custom", each Record.Field(#"Previous Step Name"{0}[[Column1]],"Column1"))

To filter out blank rows, you can select Home ribbon > Reduce Rows > Remove Blank Rows. You can also expand the down-arrow next to a column header and click Remove Empty or uncheck the null/blank values in the values list. 

Then you can perform other transformation steps.

If you are using the Folder connector to connect to and combine multiple Excel files, you can select a file as an example file and perform above transformation steps on it. Transformations on the example file will be applied to all other files automatically.

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[Anonymous]

Jing,

Very helpful!  Since I only have one week experience in PowerBI, the examples are really great.  Thanks for taking the time to include them.

Geoff

[mahoneypat]

To share sample files you need to provide a link to them on OneDrive, Google Drive, etc.  Also, see this video for how to add the Employee name as a column with the data from each sheet.

(6) Power BI - Shift N Fill Data Pattern - YouTube

Pat

[watkinnc]

To get each cell A1:

Table.AddColumn(PreviousStepName, "Employee", each Table.FirstValue(_[TableColumnName]))

Then for the blank rows:

Table.SelectRows(PriorStep, each [TableColumnName][SomeColumnInTheTable] <> null)

Then expand the table column.

--Nate