Multiple Tables

[Anonymous]

Hi 

I am trying to import data from an excel that has multiple tables formatted like this. Three tables are included, New Employees, Employee variations and Leaving Employees. 

I would like to insert another column that would classify each row as either a New Employee, Employee variations and Leaving Employees.

Is this possible? Appreciate anyone that can help.

Thanks

Mike

January
New Employees
First Name	Last Name	Type of Employment	Start Date	Job Title	Business Unit
Jack	Bean	Permanent	1/02/2018	Team Leader	People
Mike	Tyson	Fixed term	19/02/2016	Driver	Production
Sarah	Parker	Permanent	26/06/2005	Accountant	Finance
Leaving Employees
First Name	Last Name	Type of Employment	Leaving Date	Job Title	Business Unit
Tong	Lee	Fixed term	5/06/2013	Fighter	Fight Club
Harry	Potter	Permanent	3/09/2016	Magician	Howards
Employees Variations
First Name	Last Name	Type of Employment	Leaving Date	Job Title	Business Unit
Adele	Shu	Fixed tem	5/06/2013	Driver Lead	Production
Tom	Kite	Permanent	3/09/2016	Management Accountant	Finance
February
New Employees
First Name	Last Name	Type of Employment	Start Date	Job Title	Business Unit
Wednesday	Far	Permanent	1/02/2018	Team Leader	People
Leaving Employees
First Name	Last Name	Type of Employment	Leaving Date	Job Title	Business Unit
Lazy	Daizy	Fixed term	5/06/2013	Fighter	Fight Club
Mike	Turner	Permanent	3/09/2016	Magician	Howards
Larry	William	Permanent	9/09/2016	Magician	Howards
Employees Variations
First Name	Last Name	Type of Employment	Leaving Date	Job Title	Business Unit
Jenny	Singh	Fixed term	5/06/2013	Driver Lead	Production
Tina	Drive	Permanent	3/09/2016	Management Accountant	Finance
Kate	Moss	Permanent	5/06/2013	Financial Accountant	Finance

[jdbuchanan71 · Accepted solution]

Once you have your data in a stack you can add a conditional column that looks for the word "Employees" in the first column and grabs that value if it finds it.  After that you can use the fill down to tag all the rows.

let
    Source = Table,
    #"Added Conditional Column" = Table.AddColumn(Source, "Custom", each if Text.Contains([Column1], "Employee") then [Column1] else null),
    #"Filled Down" = Table.FillDown(#"Added Conditional Column",{"Custom"})
in
    #"Filled Down"

[Anonymous]

Thank you jdbuchanan71 . That absolutely worked.