Excel PowerQuery (or dax is just as perfect) - add column with unique ID

1
votes

I have a table (formatted as table) for the inputs. I want to add a unique ID column to my table.

Constraints:

  • it should not use any other columns

  • it should be constant and stable, meaning that inserting a new row won't change any row IDs, but only adds a new one.

Anything calculated from another column's value is not useful because there will be typos. So changing the ID will mean data loss in other tables connected to this one.

Simply adding an index in query editor is not useful because there will be inserted rows in the middle, and ids are recalculated at this action

I am also open for any VBA solution. I tried to write a custom function taht would add a new ID into an "rowID" column in the same row if there is no ID yet, but I failed with referencing the cells from a function called from a Table.

issue in images

1
exceluniqueidentifierpowerquery
No matter what tool you use, this seems impossible. – Mike Honey
bad news to hear. :( in fact I've figured out something: a macro first generates an id and puts into the source table and refreshes query afterwards. This is however just a workaround, because I need to keep in mind that std refresh query is not an option, I need to run my own macro for that. Indeed, this method assumes that the source table is also in the same workbook, it would fail for external data sources. – captainThe

1 Answers

1
votes

My suggestion would be to use a self referencing query.

Query "Data" below imports Excel table "Data" and also outputs to Excel table "Data".

In order to create such a query, first create a query "Data" that imports some Excel table (let's say Table1), run the query so table "Data" is created. Now you can adjust the query source from Table1 to Data and maintain this table in Excel (leaving blank IDs for new rows) and run the query to generate new IDs.

Otherwise the query should be pretty straightforward; if not: let me know where you need additional explanation.

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    Typed = Table.TransformColumnTypes(Source,{{"Col1", Int64.Type}, {"Col2", type text}, {"ID", Int64.Type}}),
    MaxID = List.Max(Typed[ID]),
    OriginalSort = Table.AddIndexColumn(Typed, "OriginalSort",1,1),
    OldRecords = Table.SelectRows(OriginalSort, each ([ID] <> null)),
    NewRecords = Table.SelectRows(OriginalSort, each ([ID] = null)),
    RemovedNullIDs = Table.RemoveColumns(NewRecords,{"ID"}),
    NewIDs = Table.AddIndexColumn(RemovedNullIDs, "ID", MaxID + 1, 1),
    NewTable = OldRecords & NewIDs,
    OriginalSortRestored = Table.Sort(NewTable,{{"OriginalSort", Order.Ascending}}),
    RemovedOriginalSort = Table.RemoveColumns(OriginalSortRestored,{"OriginalSort"})
in
    RemovedOriginalSort