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 ID Year  123 2001 456 2008 ...
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.

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