cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gdstuart
New Member

Combine Excel files and add a column identifying the employee

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.

1 ACCEPTED SOLUTION
v-jingzhang
Community Support
Community Support

Hi @gdstuart 

 

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"))

050701.jpg

050702.jpg

 

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.

View solution in original post

4 REPLIES 4
v-jingzhang
Community Support
Community Support

Hi @gdstuart 

 

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"))

050701.jpg

050702.jpg

 

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.

View solution in original post

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
Super User IV
Super User IV

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors