Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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.

Need Help
  • v-jingzhang's avatar
    v-jingzhang
    5 years ago

    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.

4 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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

  • v-jingzhang's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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