Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi, my suspicions are that this may be impossible but...
I am kind of reinventing the wheel in an attempt to increase my BI knowledge, so I am making a football based BI as it's something I have a strong interest in...
Problem is football runs from seasons, but the data I can get from available sources often works from season ranges, for example a player could be at a club from 2000-2007, but I want to create the squads based on this sort of info so I can ask 'who was in the 2000-2001 team?'
So the question: Is there a way of separating out the year groups into duplicate rows?
Edgar Chadwick | FW | 1888–1898 |
becomes
Edgar Chadwick FW 1888-1889
Edgar Chadwick FW 1889-1890
Edgar chadiwck FW 1890-1891
and so on...
Reason being I am going to get manager info and build the squads so its possible to choose a season - 1889-1890 for example and it will show Edgar Chadwick in that squad.
Hope that makes sense and any help/alternatives would be greately appreciated.
Many thanks
Wayne
Solved! Go to Solution.
@whothefoo seems interesting stuff to do. In this case you want to create fiscal calendar which is basically a season based and then have a column showing the year 2000-2001 , 2002-2003 etc. All the magic is going to come from fiscal calendar dimension. Hope it make sense. You can google on fiscal calendar and I'm sure you will get many blogs on how to use it.
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.
@whothefoo seems interesting stuff to do. In this case you want to create fiscal calendar which is basically a season based and then have a column showing the year 2000-2001 , 2002-2003 etc. All the magic is going to come from fiscal calendar dimension. Hope it make sense. You can google on fiscal calendar and I'm sure you will get many blogs on how to use it.
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.
Yes 🙂 took a bit of playing around with things I didn't understand but it's sorted now.
Thank you
If anyone is interested it's a mixture of using left and right commands to make two columns with the start and end year from the 2010-2015 style range.
Subtract the start column from the end to give a count of how many duplicate rows you need then follow the below:
https://www.youtube.com/watch?v=cXb308NPU2M
* I did have to add the final index column into the formula when expanding the rows but it's given me a duplicate row for each with a value of 0-however many dupliates there are.
Now I can just add that value to the start date and add one to create the end date and concat back to get a start to end range - for 2010-2015 - index 3 - add index (in the case 3) to start (2010) add one more for the end date and concat = 2013-2014.
For the next dipicate row the index is 4 so it will make 2014-2015.
phew lol
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |