Show only latest version

[Hawjeen]

Hi,

I came across an issue i cant solve myself, and hope maybe someone ot there could help me.

My data looks like the below, i want to somehow only show the latest version for a document and all the lines connected with that.

e.g row 10-23 should not be visible because row 24-30 is the same document but with a latere version.

Row	Document No_	Version No_	Line No_	Show
1	SO0222092	1	10000	Yes
2	SO0222096	1	10000	Yes
3	SO0222096	1	20000	Yes
4	SO0222101	1	10000	Yes
5	SO0222101	1	20000	Yes
6	SO0222114	1	10000	Yes
7	SO0222114	1	20000	Yes
8	SO0222121	1	10000	Yes
9	SO0222121	1	20000	Yes
10	SO0222129	1	10000	No
11	SO0222129	1	20000	No
12	SO0222129	1	30000	No
13	SO0222129	1	40000	No
14	SO0222129	1	50000	No
15	SO0222129	1	60000	No
16	SO0222129	1	70000	No
17	SO0222129	2	10000	No
18	SO0222129	2	20000	No
19	SO0222129	2	30000	No
20	SO0222129	2	40000	No
21	SO0222129	2	50000	No
22	SO0222129	2	60000	No
23	SO0222129	2	70000	No
24	SO0222129	3	10000	Yes
25	SO0222129	3	20000	Yes
26	SO0222129	3	30000	Yes
27	SO0222129	3	40000	Yes
28	SO0222129	3	50000	Yes
29	SO0222129	3	60000	Yes
30	SO0222129	3	70000	Yes
31	SO0222141	1	10000	No
32	SO0222141	2	10000	No
33	SO0222141	3	10000	No
34	SO0222141	4	10000	No
35	SO0222141	5	10000	No
36	SO0222141	6	10000	No
37	SO0222141	7	10000	No
38	SO0222141	8	10000	Yes
39	SO0222165	1	10000	Yes
40	SO0222187	1	10000	Yes
41	SO0222187	1	20000	Yes

I would like to do it through powerquery if possible, my current query is quite simple:

let
Kilde = Sql.Database("xxx.com", "xxx"),
#"xxx$Sales Line Archive" = Kilde{[Schema="dbo",Item="xxx$Sales Line Archive"]}[Data],
#"Filtrerede rækker" = Table.SelectRows(#"xxx$Sales Line Archive", each ([Shipment Date] = #datetime(2020, 10, 2, 0, 0, 0)) and ([Shortcut Dimension 1 Code] = "2220"))
in
#"Filtrerede rækker"

Hope someone could help me out 🙂

Regards Hawjeen

[HotChilli · Accepted solution]

Use a 'Group By' on Document No.

2 Aggregations : Max  of Version No,   and   all rows

Expand the new column, then using Table.SelectRows    filter only the rows where  MaxVersionNo = the version no from original data.

[Hawjeen]

Thank you - so simple but i simply couldnt come up with that soloution myself