Get record instance in Power Query, excluding unique ID

[Jon von der Heyden]

Hi All

I am battling to find a solution to this and perhaps not asking the right question in Google as I still cannot seem to find the answer.

I have a dataset with many columns. One column denotes a unique ID for each row. The other column values are shared with many other records. What I want to do is get the instance # of each record based on all columns except the unique ID (as in that case each row would constitute a unique instance).

I can get the instance based on the common columns, but then I don't know how to pair these back with the original unique ID's.

For example:

Unique ID	Attrib1	Attrib2	Attrib3	Instance
1​	A	X	10​	1​
2​	A	Y	15​	1​
3​	B	X	20​	1​
4​	B	X	20​	2​
5​	C	Y	30​	1​

All instances are unique except ID 4, which shares the same attributes as ID 3 (Attrib1, Attrib2, Attrib3).

It's the instance column that I am having a tough time figuring out. I can get it if I discard the Unique ID, but that is a critical key that I need to retain. Any ideas?

Many thanks
Jon

 

[GraH]

Thinking out loud here, without Excel for the moment makes it a bit too hard, but let's try with...
Merge query on it's own on the attribute columns.
Fetch the position of unique ID in those tables.

 

[smozgur]

I have a dataset with many columns. One column denotes a unique ID for each row. The other column values are shared with many other records. What I want to do is get the instance # of each record based on all columns except the unique ID (as in that case each row would constitute a unique instance).

I can get the instance based on the common columns, but then I don't know how to pair these back with the original unique ID's.

For example:

Unique ID	Attrib1	Attrib2	Attrib3	Instance
1​	A	X	10​	1​
2​	A	Y	15​	1​
3​	B	X	20​	1​
4​	B	X	20​	2​
5​	C	Y	30​	1​

All instances are unique except ID 4, which shares the same attributes as ID 3 (Attrib1, Attrib2, Attrib3).

Hi Jon. What is the result table that you want to get according to this sample data? How do you want the instance numbers and IDs to be listed for each unique row? Delimited in a column like below? Or embedded as list values for instances and IDs? (It is probably me, but a result table will help me to understand)

 

[JGordon11]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    AttribList = {"Attrib1", "Attrib2", "Attrib3"},
    tbl = Table.Group(Source, AttribList, {{"All", each _}}),
    tbl1 = Table.TransformColumns(tbl, {"All", each Table.AddIndexColumn(_, "Instance",1)}),
    tbl2 = Table.ExpandTableColumn(tbl1, "All", {"Unique ID", "Instance"}),
    Result = Table.ReorderColumns(tbl2,{"Unique ID"} & AttribList & {"Instance"})

in
    Result

delme.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Unique ID	Attrib1	Attrib2	Attrib3		Unique ID	Attrib1	Attrib2	Attrib3	Instance
2	1	A	X	10		1	A	X	10	1
3	2	A	Y	15		2	A	Y	15	1
4	3	B	X	20		3	B	X	20	1
5	4	B	X	20		4	B	X	20	2
6	5	C	Y	30		5	C	Y	30	1
7
Sheet2

 

[smozgur]

(Now I understand why I didn’t understand the question. )

 

[Jon von der Heyden]

JGordon11 - yes you nailed it - this is giving me the result I anticipate, and much more elegant than my failed concoction.

Thanks Suat also, and sorry for my poor question-asking.

 

[smozgur]

Thanks Suat also, and sorry for my poor question-asking.

No, it is totally me!

 

[GraH]

With Excel now, adding what I had in mind for reference only.

let
    Source = Excel.CurrentWorkbook(){[Name="data"]}[Content],
    Set_types = Table.TransformColumnTypes(Source,{{"Unique ID", type text}, {"Attrib1", type text}, {"Attrib2", type text}, {"Attrib3", type text}}),
    Self_join = Table.NestedJoin(Set_types, {"Attrib1", "Attrib2", "Attrib3"}, Set_types, {"Attrib1", "Attrib2", "Attrib3"}, "Sub", JoinKind.LeftOuter),
    Add_col_counter = Table.AddColumn(Self_join, "counter", each List.PositionOf([Sub][Unique ID], [Unique ID])+1),
    Remove_col_sub = Table.RemoveColumns(Add_col_counter,{"Sub"})
in
    Remove_col_sub