Forum Discussion
Creating a new row with IF query in DAX
Hi Anonymous
1. In Query Editor go to Transform > New Query > New Source > Blank Query,
2. Go to advanced editor and paste the code and rename the Query1 to ExpandMonths.
3. When in your staff data table go to Add Column > General > Invoke Custom Function.
4. Make sure you set everything as on the screenshot below.5. this will add new column "ExpandMonths", all you need to do now is click the two arrows an select expand to new rows.
Best Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.
Community ChampionCan you post some sample data please and someone will help you out?
I have two sets of data, the staff data:
Employee Full Name Employee Number Employee Start Date Employee End Date Bloggs, Joe 11111 01/04/2018 01/01/4721 Bloggs, Janet 22222 15/01/2018 15/04/2018 Bloggs, Jane 33333 30/08/2015 30/02/2019 Bloggs, Jim 44444 05/02/2017 01/01/4721 And the annual leave data, NB this only reports if someone has taken annual leave, it does not include all staff:
Employee Full Name Employee Number Annual Leave Month Bloggs, Joe 11111 1 Jun-19 Bloggs, Janet 22222 1.5 Jun-19 Bloggs, Jim 44444 6 Jun-19 Bloggs, Janet 22222 6 Jul-19 Bloggs, Jane 33333 5 Jul-19 Bloggs, Jim 44444 14 Jul-19 I need to create a table like this:
Active Month Employee Number Employee Start Date Employee End Date Annual Leave Jun-19 11111 01/04/2018 01/01/4721 1 Jun-19 22222 15/01/2018 15/04/2018 1.5 Jun-19 33333 30/08/2015 30/02/2019 0 Jun-19 44444 05/02/2017 01/01/4721 6 Jul-19 11111 01/04/2018 01/01/4721 0 Jul-19 22222 15/01/2018 15/04/2018 6 Jul-19 33333 30/08/2015 30/02/2019 5 Jul-19 44444 05/02/2017 01/01/4721 14 - HotChilli
What happened to the sick leave data?
I've just given an example set of data. Once I know how to treat two tables I'm sure I can work out how to treat three.