How can I add a new row for each unique value in a column?

[SparkyTD]

I have a table with three columns: Id, ItemName and Components representing a list of items, and their components. I want to treat each item as a component of itself, by copying its ItemName value to the Components column as a new row, and cloning all the other values (e.g. Id in this case). Since this is a bit hard to explain, I made this visualization:

I have the table on the left, and I'm trying to produce the table on the right.

The final order of the Components column doesn't matter. What's the simplest way of achieving something like this in the Power Query Editor in Excel?

[latimeria · Accepted solution]

Hi @SparkyTD ,

 Try this: 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kxR0lHyLEnN9UvMTQUynfNzC/LzUvNKipVidaKVDIFCQfn5JSBhR6h0vKMhHjkjsJwRkpwTTM7JEI8cRJ8xkpwzTM7ZEI8cUF8sAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Removed Duplicates" = Table.Distinct(#"Promoted Headers", {"Id"}),
    #"Removed Columns" = Table.RemoveColumns(#"Removed Duplicates",{"Components"}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Removed Columns", "ItemName", "Components"),
    #"Appended Query" = Table.Combine({#"Duplicated Column", #"Promoted Headers"})
in
    #"Appended Query"