Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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?
Solved! Go to 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"
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"