Forum Discussion

natasha519's avatar
natasha519
Icon for Helper II rankHelper II
4 years ago
Solved

Multiple Rows based on Unique ID

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 IDYear 
1232001
4562008

 

Desired Table:

Unique IDYearCondition
1232001New
1232001Used
1232001Old
4562008New
4562008Used
4562008Old

 

How can I acheive this in Power Query?

Need Help
  • v-jingzhang's avatar
    v-jingzhang
    4 years ago

    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.

2 Replies

  • artpil's avatar
    artpil
    Icon for Resolver II rankResolver II
    4 years ago

    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

  • v-jingzhang's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support
    4 years ago

    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.