Multiple Rows based on Unique ID

[natasha519]

I have one column with a unique ID and I want to create a new column with the following values (New, Used, Old) for each unique ID. 

Current Table:

Unique ID	Year
123	2001
456	2008

Desired Table:

Unique ID	Year	Condition
123	2001	New
123	2001	Used
123	2001	Old
456	2008	New
456	2008	Used
456	2008	Old

How can I acheive this in Power Query?

[v-jingzhang · Accepted solution]

Hi @natasha519 

Here is my solution which may be simpler. 

First add a custom column from Add Column > Custom Column with the list {"New", "Used", "Old"}

Then expand above custom column to New Rows. You will get the desired result. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[artpil]

HI,

Here's one posible solution - sql like aproach. Paste the code to advanced editor.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs3LLCxNVfB0UdJRikxNLFJQitWJVjI0MgbyjQwMDMFcE1MzCNdCKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    OriginValues = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUfJLLVeK1YGwQ4tTU+Ac/xwgOxYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TempId = _t, Origin = _t]),
    #"Added Custom" = Table.AddColumn(#"Promoted Headers", "TempId", each "a"),
    #"Merged Queries" = Table.NestedJoin(#"Added Custom", {"TempId"}, OriginValues, {"TempId"}, "Added Custom", JoinKind.LeftOuter),
    #"Expanded Added Custom" = Table.ExpandTableColumn(#"Merged Queries", "Added Custom", {"Origin"}, {"Origin"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Added Custom",{"TempId"})
in
    #"Removed Columns"

Hope this helps,

Artur