Custom Column For Matching Values

[radonwilson]

1. In Trans_Type Col, I have 3 transaction types i.e. Order, Shipping Services & Refund.
2. Now, some Order_IDs of Order (Trans_Type) are matching with Refund (Trans_Type), I want to tag these matched as "Found" and the rest as "Unfound" in Custom Column.

How to create a custom column in a power query?

Demo.xlsx
	A	B	C
1	Trans_Type	Order ID	Helper/Custom Column
2	Order	111	Not Found
3	Shipping Service	111	Not Found
4	Refund	999	Found
5	Shipping Service	222	Not Found
6	Order	222	Found
7	Order	333	Not Found
8	Order	444	Not Found
9	Order	555	Found
10	Order	666	Not Found
11	Order	777	Found
12	Order	888	Not Found
13	Order	999	Found
14	Refund	777	Found
15	Shipping Service	333	Not Found
16	Shipping Service	444	Not Found
17	Shipping Service	555	Not Found
18	Shipping Service	999	Not Found
19	Refund	222	Found
20	Refund	555	Found
21	Refund	2121	Not Found
22	Shipping Service	666	Not Found
23	Shipping Service	777	Not Found
24	Shipping Service	888	Not Found
25	Refund	4242	Not Found
26	Refund	3939	Not Found
27	Refund	5757	Not Found
Sheet1

 

[awoohaw]

I have "" for Shipping Services, but you can change that in the end of each If statement.

mr excel questions 12.xlsm
	A	B	C	D
1	Trans_Type	Order ID		Helper/Custom Column
2	Order	111	Not Found	Not Found
3	Shipping Service	111		Not Found
4	Refund	999	Found	Found
5	Shipping Service	222		Not Found
6	Order	222	Found	Found
7	Order	333	Not Found	Not Found
8	Order	444	Not Found	Not Found
9	Order	555	Found	Found
10	Order	666	Not Found	Not Found
11	Order	777	Found	Found
12	Order	888	Not Found	Not Found
13	Order	999	Found	Found
14	Refund	777	Found	Found
15	Shipping Service	333		Not Found
16	Shipping Service	444		Not Found
17	Shipping Service	555		Not Found
18	Shipping Service	999		Not Found
19	Refund	222	Found	Found
20	Refund	555	Found	Found
21	Refund	2121	Not Found	Not Found
22	Shipping Service	666		Not Found
23	Shipping Service	777		Not Found
24	Shipping Service	888		Not Found
25	Refund	4242	Not Found	Not Found
26	Refund	3939	Not Found	Not Found
27	Refund	5757	Not Found	Not Found
Sheet32

Cell Formulas
Range	Formula
C2:C27	C2	=IF(A2="Order",IF(ISNUMBER(MATCH("Refund"&B2,$A$2:$A$27&$B$2:$B$27,0)),"Found","Not Found"), IF(A2="Refund",IF(ISNUMBER(MATCH("Order"&B2,$A$2:$A$27&$B$2:$B$27,0)),"Found","Not Found"),""))

 

[radonwilson]

I have "" for Shipping Services, but you can change that in the end of each If statement.

mr excel questions 12.xlsm
	A	B	C	D
1	Trans_Type	Order ID		Helper/Custom Column
2	Order	111	Not Found	Not Found
3	Shipping Service	111		Not Found
4	Refund	999	Found	Found
5	Shipping Service	222		Not Found
6	Order	222	Found	Found
7	Order	333	Not Found	Not Found
8	Order	444	Not Found	Not Found
9	Order	555	Found	Found
10	Order	666	Not Found	Not Found
11	Order	777	Found	Found
12	Order	888	Not Found	Not Found
13	Order	999	Found	Found
14	Refund	777	Found	Found
15	Shipping Service	333		Not Found
16	Shipping Service	444		Not Found
17	Shipping Service	555		Not Found
18	Shipping Service	999		Not Found
19	Refund	222	Found	Found
20	Refund	555	Found	Found
21	Refund	2121	Not Found	Not Found
22	Shipping Service	666		Not Found
23	Shipping Service	777		Not Found
24	Shipping Service	888		Not Found
25	Refund	4242	Not Found	Not Found
26	Refund	3939	Not Found	Not Found
27	Refund	5757	Not Found	Not Found
Sheet32

Cell Formulas
Range	Formula
C2:C27	C2	=IF(A2="Order",IF(ISNUMBER(MATCH("Refund"&B2,$A$2:$A$27&$B$2:$B$27,0)),"Found","Not Found"), IF(A2="Refund",IF(ISNUMBER(MATCH("Order"&B2,$A$2:$A$27&$B$2:$B$27,0)),"Found","Not Found"),""))

Any simpler way of doing the same?

How to create a custom column in POWER QUERY?

 

[awoohaw]

yes, you could do it in power query, I'm sorry I am not a expert an custom columns there.

 

[JGordon11]

let
    Source = Table.Buffer(Excel.CurrentWorkbook(){[Name="Table1"]}[Content]),
    Result = Table.AddColumn(Source, "Found?", each 
        let 
            lst = Table.SelectRows(Source, (x)=> x[Order ID] = [Order ID] and [Trans_Type]<>"Shipping Service")[Trans_Type]
        in 
            if List.ContainsAll(lst, {"Order", "Refund"}) then "Found" else "Not Found"
    )
in
    Result

 

[radonwilson]

let
    Source = Table.Buffer(Excel.CurrentWorkbook(){[Name="Table1"]}[Content]),
    Result = Table.AddColumn(Source, "Found?", each
        let
            lst = Table.SelectRows(Source, (x)=> x[Order ID] = [Order ID] and [Trans_Type]<>"Shipping Service")[Trans_Type]
        in
            if List.ContainsAll(lst, {"Order", "Refund"}) then "Found" else "Not Found"
    )
in
    Result

Thanks for your solution it's working.

Where I can learn this power query m language from basics?

 

[radonwilson]

let
    Source = Table.Buffer(Excel.CurrentWorkbook(){[Name="Table1"]}[Content]),
    Result = Table.AddColumn(Source, "Found?", each
        let
            lst = Table.SelectRows(Source, (x)=> x[Order ID] = [Order ID] and [Trans_Type]<>"Shipping Service")[Trans_Type]
        in
            if List.ContainsAll(lst, {"Order", "Refund"}) then "Found" else "Not Found"
    )
in
    Result

could you please explain how its working?

 

[JGordon11]

Table.AddColumn goes row by row through the Source table, for each row it gets the Trans_Type column of a filtered table where that filter is OrderID matches that row's OrderID, and that and it's Trans_Type value is not Shipping Service. That value is stored as a list in the variable lst. Then it checks to see if both the words Order and Refund are in the list, if True then return the word Found in that row of the new column ([Found?]) if false then return "Not Found."

To learn M I recommend searching on YouTube where there are hundreds of tutorials.

 

[jdellasala]

One of the best PQ tutorials is a full college level class - Full Advanced Data Analysis & BI Class (MSPTDA). Power Query. I highly recommend it. It's currently 83 videos, and nearly all have start and end workbook samples to download as well as data and PDFs as needed. The channel is amazing. Don't be fooled by how old the videos are, they still teach the basic and even high level skills needed for PQ. You might want to check out the channel itself. Mike is one of the best with PQ that I've seen!

 

[radonwilson]

Table.AddColumn goes row by row through the Source table, for each row it gets the Trans_Type column of a filtered table where that filter is OrderID matches that row's OrderID, and that and it's Trans_Type value is not Shipping Service. That value is stored as a list in the variable lst. Then it checks to see if both the words Order and Refund are in the list, if True then return the word Found in that row of the new column ([Found?]) if false then return "Not Found."

To learn M I recommend searching on YouTube where there are hundreds of tutorials.

Thank you for the detailed answer.