Power Query help

[Rodel Geronimo]

hello all, kindly help me to add a custom column in my table "REV" and a column named "Document Number". This column can have multiple duplicates so I want to keep the original and append any duplicates by "-01" and so on.

Thank you so much for your help as usual.

 

[Kaper]

The main mCode could go as follows:

let
    Source = Excel.CurrentWorkbook(){[Name="REV"]}[Content],
    GroupedByDocNo = Table.Group(Source, {"Document Number"}, {{"SingleDoc", each _, type table [index=nullable number, Document Number=nullable text]}}),
    AddedIndexesForEachDoc = Table.AddColumn(GroupedByDocNo, "SingleDocIndexed", each Table.AddIndexColumn([SingleDoc], "Revisions", 0, 1)),
    ExpandTablesInSingleDocIndexed = Table.ExpandTableColumn(AddedIndexesForEachDoc, "SingleDocIndexed", {"Revisions"}, {"Revisions"}),
    AddedConditionalColumn = Table.AddColumn(ExpandTablesInSingleDocIndexed, "Document Number with Rev", each if [Revisions] = 0 then [Document Number]  else [Document Number] & "-" & Number.ToText([Revisions],"00")),
    RemoveUnneded = Table.SelectColumns(AddedConditionalColumn,{"Document Number with Rev"})
in
    RemoveUnneded

The only less intuitive (semi-manual) part is Table.AddColumn where you manually add a function which would normally be clicked-out in the menu: Table.AddIndexColumn(
Normally the first argument here wouuld be a table name, but we write a column which contains tables obtained after grouping - [SingleDoc],

You may see similar work here

Probably you shal revise also which columns are removed - with the code above only number with revisions remains. Also - if column with doc numbers is a number column it shall be either changed to text or to obtained mixed type final column use changed line

   AddedConditionalColumn = Table.AddColumn(ExpandTablesInSingleDocIndexed, "Document Number with Rev", each if [Revisions] = 0 then [Document Number]  else Number.ToText([Document Number]) & "-" & Number.ToText([Revisions],"00")),

 

[Rodel Geronimo]

Thank you so much, that worked though , added only an ascending order sort in the beginning.

 

[Kaper]

Wow, not the quickest answer

but I'm glad it worked for you