On duplicate rows of first column move data in other columns leaving only unique values in first column

Power Query
1

I need to remove the duplicate rows in the Original Order column, however I need to move any data on the duplicate row to other columns, such that unique values are shown in the Original Order column and any Rework Order is shown in the Rework Order columns, the image below shows the structure of the staring data.

Original Table

Below is the required table structure

Ideally the data in the Rework Order columns would be in ascending order.

Desired Table
Desired Table1084×137 5.55 KB

The number of Rework Order columns would need to be expendable depending upon how many Rework Orders are connected to the Parent Order.

Duplicates.pbix (29.6 KB)
data FY24 Jul.xlsx (13.0 KB)

2

Hi @jprlimey,

See how this works for you. Just copy the full script into a new blank query.

let
    ColCount = List.Max ( List.Transform( GroupRows[Rework Order], List.Count )),
    FieldNames = List.Buffer( List.Transform( {1..ColCount}, each "Rework Order " & Number.ToText(_))),
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fY5BDsAgCAT/4tmDyILyFtP/f6PWqk1qbcKBZGZZSnHEkEBw3mkAWK1ufQ7fsIHjjTkJvbEJyDrGcyfkH0u2lraCy6qJtWpiRFuxKuvsGC9rstFGnUKNPjJZ8uRxezmLTSsM6zgB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Original Order" = _t, #"Rework Order-1" = _t, #"Rework Order-2" = _t, #"Rework Order-3" = _t, #"Rework Order-4" = _t]),
    GroupRows = Table.Group(Source, {"Original Order"}, {{"Rework Order", each List.RemoveMatchingItems( List.Combine( List.Skip( Table.ToColumns( _ ), 1 )), {"", null}) }}),
    CreateTable = Table.TransformColumns( GroupRows, {{"Rework Order", each Table.FromRows( {_}, List.FirstN( FieldNames, List.Count(_)) ) }}),
    ExpandColumns = Table.ExpandTableColumn(CreateTable, "Rework Order", FieldNames )
in
    ExpandColumns

with this result

image
image1483×142 5.98 KB

I hope this is helpful

1 Like
3

What a cool powerquery-m pattern!

4

Let’s hope it works in the real-world :wink:

5

Even if it needs any tweaks, this could be the basis to run the patten on your always awesome LN posts :slight_smile: I am really curious to see how this plays out initially and on scale.

1 Like
6

@Melissa
O my goodness! It worked brilliantly, you are indeed the Power Query master.

So now to scale it up to a larger table. The Source and Json stuff below, how would you change the source to an actual table in my model? The source table will be coming from a SQL table, for simplicity let’s say the source table is named ProdOrders

Regards
J

let
ColCount = List.Max ( List.Transform( GroupRows[Rework Order], List.Count )),
FieldNames = List.Buffer( List.Transform( {1…ColCount}, each "Rework Order " & Number.ToText(_))),
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(“fY5BDsAgCAT/4tmDyILyFtP/f6PWqk1qbcKBZGZZSnHEkEBw3mkAWK1ufQ7fsIHjjTkJvbEJyDrGcyfkH0u2lraCy6qJtWpiRFuxKuvsGC9rstFGnUKNPjJZ8uRxezmLTSsM6zgB”, BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#“Original Order” = _t, #“Rework Order-1” = _t, #“Rework Order-2” = _t, #“Rework Order-3” = t, #“Rework Order-4” = t]),
GroupRows = Table.Group(Source, {“Original Order”}, {{“Rework Order”, each List.RemoveMatchingItems( List.Combine( List.Skip( Table.ToColumns( _ ), 1 )), {“”, null}) }}),
CreateTable = Table.TransformColumns( GroupRows, {{“Rework Order”, each Table.FromRows( {}, List.FirstN( FieldNames, List.Count()) ) }}),
ExpandColumns = Table.ExpandTableColumn(CreateTable, “Rework Order”, FieldNames )
in
ExpandColumns

7

@Melissa

Melissa,

To expand this to capture more Rework Order columns it appears I need to add the additional column names ie. #“Rework Order-5” = _t, #“Rework Order-6” = _t, #“Rework Order-7” = _t etc. etc to the line below?

let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#“Original Order” = _t, #“Rework Order-1” = _t, #“Rework Order-2” = _t, #“Rework Order-3” = _t, #“Rework Order-4” = _t]),

Best Regards
J

8

Hi @jprlimey,

You can reference the ProdOrder query in the source step, don’t forget there needs to be a comma at the end of that line. Also note that the GroupRows step, groups your data on a column called “Original Order” which needs to be present OR updated to reflect the actual column required.
Basically make sure the structure of your ProdOrders table matches the sample you provided.

let
    ColCount = List.Max ( List.Transform( GroupRows[Rework Order], List.Count )),
    FieldNames = List.Buffer( List.Transform( {1..ColCount}, each "Rework Order " & Number.ToText(_))),
    Source = ProdOrders,
    GroupRows = Table.Group(Source, {"Original Order"}, {{"Rework Order", each List.RemoveMatchingItems( List.Combine( List.Skip( Table.ToColumns( _ ), 1 )), {"", null}) }}),
    CreateTable = Table.TransformColumns( GroupRows, {{"Rework Order", each Table.FromRows( {_}, List.FirstN( FieldNames, List.Count(_)) ) }}),
    ExpandColumns = Table.ExpandTableColumn(CreateTable, "Rework Order", FieldNames )
in
    ExpandColumns
2 Likes
9

Are you secretly an Iron Chef Legend also :slight_smile:

10

secret ingredient :white_check_mark:
love a challenge :white_check_mark:
¯_(ツ)_/¯

3 Likes
11

You put the fun back into doing all of this.

12

Likewise, and thank you for your support on the forum
:+1:

1 Like
13

25+ years of managing the same types of questions, just different stuff.

1 Like
14

@Melissa

I worked perfectly, I can’t count how many hours I spent trying various approaches, this is really “sweet”, thanks you so much for the assistance.

Best Regards
J

3 Likes
15

Hi @jprlimey,

Alejandro Simón had a different approach, to your challenge. That I wanted to share with you on his behalve. It’s brilliant and more optimized.

The key is leveraging Table.Combine removing the need to create ‘the maximum number of columns’ for each row.

let
Source = ProdOrders,
Sol = Table.Combine(Table.Group(Source, {"Original Order"}, {{"All", each
    let
        a = List.RemoveMatchingItems( List.Combine( List.Skip( Table.ToColumns(_), 1)), {null, ""}),
        b = {[Original Order]{0}} & a,
        c = Table.FromRows( {b}, {"Original Order"} & List.Transform( {1..List.Count(a) }, each "Rework Order-" & Text.From(_)))
        in c }} ) [All] )
    in
Sol

Kudo’s Alejandro, thanks for sharing!

3 Likes
16

@Melissa

I now have 2 superb M-code solution thanks to Alejandro and yourself. Again thanks so much for the assistance. Now onto creating DAX parent to child measures.

One other comment, if you look at the Original Order column in either solution we have 8 Rework Orders, is there a possibility to put each rework order into the columns such that they are in ascending order. For example the Rework Order-1 column would be 6043412, the Rework Order-2 would be 6043679, Rework Order-3 would be 6043858 etc. etc.

Best Regards
J

3 Likes
17

@jprlimey can you confirm how would that look for the entire set?
Thanks

Original Table

1 Like
18

@Melissa

The image below is how it should look,

Sorted Table
Sorted Table1079×131 4.66 KB

But I do see an issue with Original Order 1395419, it is showing Rework Order 6044308 twice, and thus will be seen twice in the solution. If duplicate Rework Order numbers are seen against an Original Order, then only a single unique Rework Order numbers needs returned.

Best Regards
J

1 Like
19

Iteration towards a working solution and an outcome as a utility is exciting!

20

Hi @jprlimey,

Duplicates or sorting, that is no problem at all.

let
Source = ProdOrders,
Result = Table.Combine( Table.Group( Source, {"Original Order"}, {{"All", each
    let
        a = List.Distinct( List.RemoveMatchingItems( List.Combine( Table.ToColumns(_)), {null, ""})),
        b = {[Original Order]{0}} & List.Sort( List.RemoveFirstN( a, 1 )),
        c = Table.FromRows( {b}, {"Original Order"} & List.Transform( {1..List.Count(a)-1 }, each "Rework Order-" & Text.From(_)))
        in c }} ) [All] )
    in
Result

With this result

image
image1473×138 8.72 KB

Cheers

1 Like