vlookup with countifs and isnumber to power query

[fayez_MrExcel]

Im new in Power query, i'd like to convert my excel formula in power query (adding custom column). Basically, if the position has *contractor" in it, then value is "Contractor". if the position code appears in the supervisor column, if so then return value as "Supervisory" otherwise "Individual Contributor"

Book1
	A	B	C	D
1	Position	Position Title	Supervisor	Job Level
2	50083048	Section Head, Network & Unified Coms	50087289	Supervisor
3	50089906	Manager, HDPE	50086143	Individual Contributor
4	50084476	Chief Information Officer	50090211	Supervisor
5	50087059	Supt, Operation - PP	50089908	Individual Contributor
6	50087256	Manager, HRBP Corporate Functions	50084475	Individual Contributor
7	50087289	Manager, Infrastructure & Operations	50084476	Supervisor
8	50090918	Contractor Online	50086575	Contractor
9	50086622	Geo Contractor	50086634	Contractor
10	50086688		50083048	Individual Contributor
ZORG

Cell Formulas
Range	Formula
D2:D10	D2	=IF(COUNTIFS([@[Position Title]],"*Contractor*"),"Contractor",IF(ISNUMBER(MATCH([@Position]&"",[Supervisor]&"",0)),"Supervisor","Individual Contributor"))

 

[lrobbo314]

How about this?

PQ
	A	B	C	D
1	Position	Position Title	Supervisor	Job Level
2	50083048	Section Head, Network & Unified Coms	50087289	Supervisor
3	50089906	Manager, HDPE	50086143	Individual Contractor
4	50084476	Chief Information Officer	50090211	Supervisor
5	50087059	Supt, Operation - PP	50089908	Individual Contractor
6	50087256	Manager, HRBP Corporate Functions	50084475	Individual Contractor
7	50087289	Manager, Infrastructure & Operations	50084476	Supervisor
8	50090918	Contractor Online	50086575	Contractor
9	50086622	Geo Contractor	50086634	Contractor
10	50086688		50083048	Individual Contractor
Table3

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    JobLevel = Table.AddColumn(
        Source, "Job Level", 
        each try 
            if Text.Contains([Position Title],"Contractor") then "Contractor" 
            else if List.Contains(Source[Supervisor],[Position]) then "Supervisor" 
            else "Individual Contractor" 
        otherwise "Individual Contractor"
    )
in
    JobLevel

 

[fayez_MrExcel]

How about this?

PQ
	A	B	C	D
1	Position	Position Title	Supervisor	Job Level
2	50083048	Section Head, Network & Unified Coms	50087289	Supervisor
3	50089906	Manager, HDPE	50086143	Individual Contractor
4	50084476	Chief Information Officer	50090211	Supervisor
5	50087059	Supt, Operation - PP	50089908	Individual Contractor
6	50087256	Manager, HRBP Corporate Functions	50084475	Individual Contractor
7	50087289	Manager, Infrastructure & Operations	50084476	Supervisor
8	50090918	Contractor Online	50086575	Contractor
9	50086622	Geo Contractor	50086634	Contractor
10	50086688		50083048	Individual Contractor
Table3

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    JobLevel = Table.AddColumn(
        Source, "Job Level",
        each try
            if Text.Contains([Position Title],"Contractor") then "Contractor"
            else if List.Contains(Source[Supervisor],[Position]) then "Supervisor"
            else "Individual Contractor"
        otherwise "Individual Contractor"
    )
in
    JobLevel

lrobbo314​

How about this?

PQ
	A	B	C	D
1	Position	Position Title	Supervisor	Job Level
2	50083048	Section Head, Network & Unified Coms	50087289	Supervisor
3	50089906	Manager, HDPE	50086143	Individual Contractor
4	50084476	Chief Information Officer	50090211	Supervisor
5	50087059	Supt, Operation - PP	50089908	Individual Contractor
6	50087256	Manager, HRBP Corporate Functions	50084475	Individual Contractor
7	50087289	Manager, Infrastructure & Operations	50084476	Supervisor
8	50090918	Contractor Online	50086575	Contractor
9	50086622	Geo Contractor	50086634	Contractor
10	50086688		50083048	Individual Contractor
Table3

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    JobLevel = Table.AddColumn(
        Source, "Job Level",
        each try
            if Text.Contains([Position Title],"Contractor") then "Contractor"
            else if List.Contains(Source[Supervisor],[Position]) then "Supervisor"
            else "Individual Contractor"
        otherwise "Individual Contractor"
    )
in
    JobLevel

How about this?

PQ
	A	B	C	D
1	Position	Position Title	Supervisor	Job Level
2	50083048	Section Head, Network & Unified Coms	50087289	Supervisor
3	50089906	Manager, HDPE	50086143	Individual Contractor
4	50084476	Chief Information Officer	50090211	Supervisor
5	50087059	Supt, Operation - PP	50089908	Individual Contractor
6	50087256	Manager, HRBP Corporate Functions	50084475	Individual Contractor
7	50087289	Manager, Infrastructure & Operations	50084476	Supervisor
8	50090918	Contractor Online	50086575	Contractor
9	50086622	Geo Contractor	50086634	Contractor
10	50086688		50083048	Individual Contractor
Table3

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    JobLevel = Table.AddColumn(
        Source, "Job Level",
        each try
            if Text.Contains([Position Title],"Contractor") then "Contractor"
            else if List.Contains(Source[Supervisor],[Position]) then "Supervisor"
            else "Individual Contractor"
        otherwise "Individual Contractor"
    )
in
    JobLevel

lrobbo314 thank you for the response. However, my table source is within the PQ itself. Im trying to replicate your formula but getting an error. ​

 

[lrobbo314]

You're pasting all the code into the add column step.

I don't know what you mean by, my table source is in PQ itself.

If you have some steps already or if you just loaded the table in, you just need to add the JobLevel line of code. The JobLevel line needs to reference whatever the previous step was as the first argument.

So, copy the JobLevel line of code and go to your query. Open the advanced editor. Paste the line of code at the end before the 'in' statement. Make whatever adjustments you need. Make sure you reference the JobLevel step as the last part of the query. e.g.

in
    JobLevel

is how the last 2 lines in the query should look.