create a one product ID

Hi AhmedElbeltagy 

See if you can make something like this work.

• Create a key to group your products by. For example is ProductName something that is unique for each set or do you require additional key(s) using other fields in your data set or maybe splitting the Description...
• Group By the keys
• Add an index
• Transform the nested table, adding the New ProductID

To illustrate, copy this into a new blank query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjAwMFTSUXJU0DCKMNYEslzAXKVYHbCcEUTOMMLEAFPSGMhxQmgEqXWCyZlA5OAaUSRNgRxnkKgxmBEbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ProductID = _t, Description = _t, #"New ProductID" = _t, ProductName = _t]),
    AddAnotherKey = Table.AddColumn(Source, "Text Before Delimiter", each Text.BeforeDelimiter([Description], "("), type text),
    GroupRows = Table.Group(AddAnotherKey, {"ProductName", "Text Before Delimiter"}, {{"t", each _, type table [ProductID=nullable text, Description=nullable text, New ProductID=nullable text, ProductName=nullable text, Text Before Delimiter=text]}}),
    AddIndex = Table.AddIndexColumn(GroupRows, "Index", 1, 1, Int64.Type),
    InsertNewID = Table.ReplaceValue(AddIndex,each [t],each Table.AddColumn( [t], "New ID", (x)=> "D" & Text.From( [Index] )),Replacer.ReplaceValue,{"t"}),
    DelCols = Table.RemoveColumns(InsertNewID,{"Index", "Text Before Delimiter"}),
    Expand = Table.ExpandTableColumn(DelCols, "t", {"ProductID", "Description", "New ProductID", "New ID"}, {"ProductID", "Description", "New ProductID", "New ID"})
in
    Expand

Of course you will need to amend this to fit your actual requirement.

Ps. If this helps solve your query please mark this post as Solution, thanks!