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

Add Multiple Values in Column per ID

Hi,

 

I have a table of IDs that derive from another table, I need to add a list of values for each ID (years) so that I can summarise data into a table for each year. The years are as a list in another table. Can do this either in PowerQuery or PowerBi, whichever makes sense... (I assumed the former, but haven't had any luck with either so far).

 

Essentially I have this:

ID
1
2
3
4
5
6
7
8
9

 

And i need this:

IDAY
12017
12018
12019
12020
22017
22018
22019
22020
32017

 

I assumed it'd be easy, I've found a lot of DAX to be fairly straight forward so far... But apparently not!

Any help would be greatly appreciated.

C

2 ACCEPTED SOLUTIONS
parry2k
Super User III
Super User III

@Kureigu it will be easier to do in PQ, merge your ID table with the 2nd table which has the year on the ID column, and after the merge step, expand the column you just created and you will get all the years for each ID.

 

Something like this:

 

parry2k_0-1611446011476.png

 

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

 

 

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

sanalytics
Responsive Resident
Responsive Resident

Hello @Kureigu ,

 

Create an additional column and do cross join.

 

Refer the pbix file on below link

 

https://dropfiles.org/rEba7WTm

 

Regards

sanalytics

If it is your solution then please like and accept it as your solution

View solution in original post

3 REPLIES 3
Kureigu
Frequent Visitor

Thanks guys, I really appreciated it... I seriously doubt I would've been able to discover that solution without your help.

sanalytics
Responsive Resident
Responsive Resident

Hello @Kureigu ,

 

Create an additional column and do cross join.

 

Refer the pbix file on below link

 

https://dropfiles.org/rEba7WTm

 

Regards

sanalytics

If it is your solution then please like and accept it as your solution

View solution in original post

parry2k
Super User III
Super User III

@Kureigu it will be easier to do in PQ, merge your ID table with the 2nd table which has the year on the ID column, and after the merge step, expand the column you just created and you will get all the years for each ID.

 

Something like this:

 

parry2k_0-1611446011476.png

 

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

 

 

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

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.