Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
ID | AY |
1 | 2017 |
1 | 2018 |
1 | 2019 |
1 | 2020 |
2 | 2017 |
2 | 2018 |
2 | 2019 |
2 | 2020 |
3 | 2017 |
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
Solved! Go to Solution.
@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:
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.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
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.
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
@sabilahmed what is the final output you are looking for?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
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.
Thanks guys, I really appreciated it... I seriously doubt I would've been able to discover that solution without your help.
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
@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:
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.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
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.
Hello @parry2k
Great solution. I was attempting somethjing similar with my example data set:
Table 1:
Product ID | Lifespan (Years) | Activation year | Purchase value |
400 | 50 | 2011 | £500 |
401 | 50 | 1975 | £10,000 |
402 | 50 | 1950 | £12,000 |
403 | 30 | 2010 | £50,000 |
404 | 40 | 2020 | £40,000 |
Table 2:
Year |
1970 |
1971 |
1972 |
1973 |
1974 |
1975 |
1976 |
1977 |
1978 |
1979 |
1980 |
1981 |
1982 |
1983 |
1984 |
1985 |
1986 |
But there is no ID against the Year column in table 2. So how can I create the merge with the ID relationship. I need the exact same output in your solution buit can't do it without the ID key to connect the two.
Hoppe that makes sense.
S
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |