Create a Table for uniqueID - concatenate value for each year

[Anonymous]

Hello Everyone, 

I have a table with some product ID infromarion. Example - 

I want to use Year and Product No combined as a unique value. Example - 2019ABCDEFGH, 2020BCDEFGHI

The product table does not have the year. The goal is to have a list of product numbers combined with every year we want. Example - considering we want to 2019, 2020, 2021. My final product should look like this - 

Eevery product no combined with every year. 

(2019 Product No, 2020 Product No and 2021 Product no and an appended Query)
Currently the dataset has 3 queries that individually add each year and then one combined query - which is quite clunky. 

Is there an easier way to do this ? I can have a table with all the years we want. I wanted to know if this can be resolved in a different way

Thank you so much in advance ! 🙂 

[Jimmy801 · Accepted solution]

Hello @Anonymous 

add a new column to your table with this formula

List.Transform({"2019", "2020","2021"}, (year)=> year & [Product No])

and remove all other columns afterwards. At the end expand all list-items.

Here the complete code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRydlHSUaoA4kSlWJ1oJRdXt2AgpxKIk5RiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Product No" = _t, Type = _t, Category = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product No", type text}, {"Type", type text}, {"Category", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Year&ProductNo", each List.Transform({"2019", "2020","2021"}, (year)=> year & [Product No])),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Year&ProductNo"}),
    #"Expanded Year&ProductNo" = Table.ExpandListColumn(#"Removed Other Columns", "Year&ProductNo")
in
    #"Expanded Year&ProductNo"

transforms this

into this

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[JW_van_Holst]

=List.Transform({"2019","2020","2021"}, (OL)=> List.Transform({"ABCDEFGH", "BCDEFGHI"}, (IL)=>OL & IL))