Forum Discussion
Split Table and Re-Join
Dear all,
after hours of trying I finally resigned as I did not do any step forward =/
I am even not sure if I really need to Split and Re-Join the table, but I have a working model in KNIME which exactly does this and is working. Nevertheless I need to rebuild this in Power BI.
Maybe let me first try to describe the situation:
I have one list, which contains information on indivudual hirings and leavings. In case of leavings of course, these appear at least 2 times in the list, in case of Re-Entries over years even more. I need to evaluate these employees who left already again within first 6 months after hiring, but the information is "hidden" in multiple rows.
What I did in KNIME: I splitted hirings and leavings and used left outer join to just add the month of exit to the hiring list, calculated the difference of both dates and that´s it.
As I might need to split the list with CREATETABLE I am not able to use the MERGE QUERIES in Power Query Editor (or?).
I already created 2 seperate tables but could not join them together using GENERATE/ALL or NATURALLEFTOUTERJOIN although I created a relationship of the Person ID.
6 Replies
Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
sc4ry It is not making it clear. Also was there a need to split table. Check this HR solution in my blog if that can help.
Hey,
the difference between our tables is exactly my issue. My data looks like the following:
Employee Period HC Change
A 12/19 +1
B 01/20 +1
C 01/20 +1
A 02/20 -1
C 03/20 -1
Out of this I want to create a table like you have.
Employee Entry Period Exit Period
A 12/19 02/20
C 01/20 03/20
Hi sc4ry ,
1, Go to edit queries and split your table into 3 columns:
2.Then you need 2 measures as below:
Hiring = CALCULATE(MIN('Table'[Column2]),ALLEXCEPT('Table','Table'[Column1]),'Table'[Column3]=1)leaving = CALCULATE(MAX('Table'[Column2]),ALLEXCEPT('Table','Table'[Column1]),FILTER('Table',DATEDIFF(MAX('Table'[Column2]),'Table'[Hiring],MONTH)<6 &&'Table'[Column3]=-1))3.Finally you will see:
For the related .pbix file,pls click here.
Best Regards,
KellyDid I answer your question? Mark my post as a solution!
Hey,
of course somehow your measures are working but at the end it does not help to solve my problem. Of course this is very much related to my bad description. But due to your formulas, maybe I do not even need a new table but just can solve this with some new columns in the existing one.
Please see below a very shortend part of my table. It has much more columns and thousands of rows as this table mixes per month the active employees (column FTE/HC) as well as hirings and leavings (column FTE Change/HC Change). Due to FTE-Splits, we even can have multiple columns for active employees as well as hirings and leavings. Good thing is, that we need to solve it for HC anyway, so we can focus on HC Change (+1 = hirings, -1 = leavings).
Due to seasonal work, employees might even appear multiple times as hiring and leaving, like in the example.
The columns I need formulas for, are the last 3 ones, whereas "Hiring Period" might not be needed or could be easily solved with something like: IF(Details[HC Change]=1;Details[Period];BLANK())
Drop-Off Diff is also not really a big challange, but how to calculate the Leaving Period? If possible, I would always like to check for the Leaving Period right after the Hiring Period.
I hope I could make it as clear as possible and the table is usefull too.
Period Person ID FTE HC FTE Change HC Change Leaving Period Hiring Period Drop-Off Diff 01.07.2018 XYZ 1 1 1,00 1 01.09.2018 01.07.2018 2 01.08.2018 XYZ 1 1 0,00 0 01.09.2018 XYZ 0 0 -1,00 -1 01.07.2019 XYZ 1 1 1,00 1 01.09.2019 01.07.2019 2 01.08.2019 XYZ 1 1 0,00 0 01.09.2019 XYZ 0 0 -1,00 -1 Sorry, that it was not that clear from the beginning, but due to the measures I came to the conclusion, that it would be even much better to add this to the existing table instead of creating a new one.
