Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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.
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
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
To learn more about Power BI, follow me on Twitter or subscribe 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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.