Extract specific data from a spreadsheet

[Lesjoan01]

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

Company

Location

Payroll Number

Title

Firstname

Lastname

Gender

Job Title

Car or Cash

Cash Amount

Hours per week

Salary

Age

Start Date

Years Service

ABC Ltd

Site 1

010

Mr

Paul

Smith

M

IT Director

Car

40

£52,000

41

02/08/1995

23

DEF Ltd

Site 2

020

Miss

Mary

Jones

F

PA

Cash

£1,500

40

£34,000

35

12/11/2000

18

ABC Ltd

Site 1

030

Mr

Steve

Green

M

Finance Director

Car

40

£50,000

40

15/03/1998

20

GHI Ltd

Site 3

040

Mrs

Sarah

Brown

F

Head of HR

Car

40

£48,000

39

23/07/2000

18

DEF Ltd

Site 2

050

Mrs

Jane

Greaves

F

L&D Director

Car

40

£46,000

41

07/10/1997

21

GHI Ltd

Site 3

060

Mr

Graham

Stephens

M

Foreman

Cash

£1,200

37

£29,000

37

22/05/2005

13

DEF Ltd

Site 2

070

Mr

Len

Cloth

M

Shift Supervisor

Cash

£1,400

40

£31,000

40

06/03/1999

19

<tbody>
</tbody>

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