cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
newbee08
Frequent Visitor

Create a Table for uniqueID - concatenate value for each year

Hello Everyone, 

 

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

newbee08_0-1613490815409.png

 


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. 

newbee08_1-1613490847216.png

 


(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 ! 🙂 

 

1 ACCEPTED SOLUTION
Jimmy801
Super User III
Super User III

Hello @newbee08 

 

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

Jimmy801_0-1613542744550.png

into this

Jimmy801_1-1613542753649.png

 

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

View solution in original post

2 REPLIES 2
Jimmy801
Super User III
Super User III

Hello @newbee08 

 

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

Jimmy801_0-1613542744550.png

into this

Jimmy801_1-1613542753649.png

 

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

View solution in original post

JW_van_Holst
Resolver III
Resolver III

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors