Extract specific data from a spreadsheet

[Lesjoan01]

[TABLE="class: grid, width: 1000, align: center"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[TD]K
[/TD]
[TD]L
[/TD]
[TD]M
[/TD]
[TD]N
[/TD]
[TD]O
[/TD]
[/TR]
[TR]
[TD]Company
[/TD]
[TD]Location
[/TD]
[TD]Payroll Number
[/TD]
[TD]Title
[/TD]
[TD]Firstname
[/TD]
[TD]Lastname
[/TD]
[TD]Gender
[/TD]
[TD]Job Title
[/TD]
[TD]Car or Cash
[/TD]
[TD]Cash Amount
[/TD]
[TD]Hours per week
[/TD]
[TD]Salary
[/TD]
[TD]Age
[/TD]
[TD]Start Date
[/TD]
[TD]Years Service
[/TD]
[/TR]
[TR]
[TD]ABC Ltd
[/TD]
[TD]Site 1
[/TD]
[TD]010
[/TD]
[TD]Mr
[/TD]
[TD]Paul
[/TD]
[TD]Smith
[/TD]
[TD]M
[/TD]
[TD]IT Director
[/TD]
[TD]Car
[/TD]
[TD][/TD]
[TD]40
[/TD]
[TD]£52,000
[/TD]
[TD]41
[/TD]
[TD]02/08/1995
[/TD]
[TD]23
[/TD]
[/TR]
[TR]
[TD]DEF Ltd
[/TD]
[TD]Site 2
[/TD]
[TD]020
[/TD]
[TD]Miss
[/TD]
[TD]Mary
[/TD]
[TD]Jones
[/TD]
[TD]F
[/TD]
[TD]PA
[/TD]
[TD]Cash
[/TD]
[TD]£1,500
[/TD]
[TD]40
[/TD]
[TD]£34,000
[/TD]
[TD]35
[/TD]
[TD]12/11/2000
[/TD]
[TD]18
[/TD]
[/TR]
[TR]
[TD]ABC Ltd
[/TD]
[TD]Site 1
[/TD]
[TD]030
[/TD]
[TD]Mr
[/TD]
[TD]Steve
[/TD]
[TD]Green
[/TD]
[TD]M
[/TD]
[TD]Finance Director
[/TD]
[TD]Car
[/TD]
[TD][/TD]
[TD]40
[/TD]
[TD]£50,000
[/TD]
[TD]40
[/TD]
[TD]15/03/1998
[/TD]
[TD]20
[/TD]
[/TR]
[TR]
[TD]GHI Ltd
[/TD]
[TD]Site 3
[/TD]
[TD]040
[/TD]
[TD]Mrs
[/TD]
[TD]Sarah
[/TD]
[TD]Brown
[/TD]
[TD]F
[/TD]
[TD]Head of HR
[/TD]
[TD]Car
[/TD]
[TD][/TD]
[TD]40
[/TD]
[TD]£48,000
[/TD]
[TD]39
[/TD]
[TD]23/07/2000
[/TD]
[TD]18
[/TD]
[/TR]
[TR]
[TD]DEF Ltd
[/TD]
[TD]Site 2
[/TD]
[TD]050
[/TD]
[TD]Mrs
[/TD]
[TD]Jane
[/TD]
[TD]Greaves
[/TD]
[TD]F
[/TD]
[TD]L&D Director
[/TD]
[TD]Car
[/TD]
[TD][/TD]
[TD]40
[/TD]
[TD]£46,000
[/TD]
[TD]41
[/TD]
[TD]07/10/1997
[/TD]
[TD]21
[/TD]
[/TR]
[TR]
[TD]GHI Ltd
[/TD]
[TD]Site 3
[/TD]
[TD]060
[/TD]
[TD]Mr
[/TD]
[TD]Graham
[/TD]
[TD]Stephens
[/TD]
[TD]M
[/TD]
[TD]Foreman
[/TD]
[TD]Cash
[/TD]
[TD]£1,200
[/TD]
[TD]37
[/TD]
[TD]£29,000
[/TD]
[TD]37
[/TD]
[TD]22/05/2005
[/TD]
[TD]13
[/TD]
[/TR]
[TR]
[TD]DEF Ltd
[/TD]
[TD]Site 2
[/TD]
[TD]070
[/TD]
[TD]Mr
[/TD]
[TD]Len
[/TD]
[TD]Cloth
[/TD]
[TD]M
[/TD]
[TD]Shift Supervisor
[/TD]
[TD]Cash
[/TD]
[TD]£1,400
[/TD]
[TD]40
[/TD]
[TD]£31,000
[/TD]
[TD]40
[/TD]
[TD]06/03/1999
[/TD]
[TD]19
[/TD]
[/TR]
</tbody>[/TABLE]
Hi
I want to extract specific data from a spreadsheet, an example of the data is above

I want to initially extract data from columns:

A
B
C
E
J
L
M

and then place that data on a separate sheet. Is there a formula to do this?

Can anyone help?

Big Thanks
Lesley

 

[alansidman]

Name your data table as Table1. Then open a blank query in Power Query/Get and Transform. Insert this Mcode on the Advance Tab

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Company", type text}, {"Location", type text}, {"Payroll Number", Int64.Type}, {"Title", type text}, {"Firstname", type text}, {"Lastname", type text}, {"Gender", type text}, {"Job Title", type text}, {"Car or Cash", type text}, {"Cash Amount", type text}, {"Hours per week", Int64.Type}, {"Salary", type text}, {"Age", Int64.Type}, {"Start Date", type any}, {"Years Service", Int64.Type}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Company", "Location", "Payroll Number", "Firstname", "Cash Amount", "Salary", "Age"})
in
    #"Removed Other Columns"

 

[Lesjoan01]

Hi Alan,

that is great, thank you very much.

One other question (if you don't mind) - If I wanted to extract all the data for a specific company would I enter {{"Company = ABC Ltd", type text}

Sorry, I am new to power query.

Many thanks

Lesley

 

[alansidman]

Just filter the column having the specific data.

 

[Lesjoan01]

Ok, that's great.

Thanks very much for you assistance with my query.

Regards

Lesley