Power Query: Find old procedure and match with new one into a new query.

[mduntley]

I have a table I am doing in power query, but i am trying to do something that i cant find out


I have a query, which has column a to c with other data. I am trying to make it so in another query it to show what is the old procedure and what is the new one.

The new Procedure is the MAX of the CPT count, and all the old are the ones that are not the max count. I need a new query as i need to preserve the original one

Book1
	A	B	C	D	E	F	G
1	Query 1				Query 2
2	procedure	cpt code	Count		Old Procedure	CPT	New Procedure
3	761000813	36591	1		761000813	36591	300000003
4	300000003	36591	34		260000004	96361	510000097
5	260000004	96361	2		260000001	96365	510000098
6	510000097	96361	39		260000003	96366	510000455
7	260000001	96365	14		720000022	96367	940000017
8	510000098	96365	29		456000115	96368	450000137
9	260000003	96366	12		260000008	96372	510000100
10	510000455	96366	26		761000961	96402	331000002
11	720000022	96367	5		510000104	96417	335000005
12	940000017	96367	29		280000023	96523	510000106
13	456000115	96368	1		761000046	96523	510000106
14	450000137	96368	24
15	260000008	96372	2
16	510000100	96372	54
17	761000961	96402	1
18	331000002	96402	16
19	510000104	96417	4
20	335000005	96417	13
21	280000023	96523	2
22	761000046	96523	5
23	510000106	96523	25
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G13	Expression	=#REF!<>$B13	text	NO
A3:C22	Expression	=$B2<>$B3	text	NO
A3:C23	Expression	=#REF!<>$B3	text	NO

 

[mduntley]

Please disregarded the condition formatting, If its cant be done on a new query, i can make due with the same query

 

[alansidman]

Load your table to Power Query Editor.
Duplicate your table (PQ will name it Table1 (2)
Then Join the two tables in Left Outer Join based on the cpt code

Here is the Mcode to show that join and subsequent steps to achieve the expected results.

let
    Source = Table.NestedJoin(Table1, {"cpt code"}, #"Table1 (2)", {"cpt code"}, "Table1 (2)", JoinKind.LeftOuter),
    #"Expanded Table1 (2)" = Table.ExpandTableColumn(Source, "Table1 (2)", {"procedure"}, {"Table1 (2).procedure"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Table1 (2)", "Custom", each if [procedure]=[#"Table1 (2).procedure"] then null else [procedure]),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] <> null)),
    #"Removed Duplicates" = Table.Distinct(#"Filtered Rows", {"cpt code"}),
    #"Removed Columns" = Table.RemoveColumns(#"Removed Duplicates",{"Count", "Custom"})
in
    #"Removed Columns"