Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
SparkyTD
New Member

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

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:

SparkyTD_0-1678828789296.png

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?

 

1 ACCEPTED SOLUTION
latimeria
Solution Specialist
Solution Specialist

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"

latimeria_0-1678836245510.png

 

View solution in original post

1 REPLY 1
latimeria
Solution Specialist
Solution Specialist

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"

latimeria_0-1678836245510.png

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.