Forum Discussion
Helper VPowerbi
this is my table 1 and table 2.
Level 3 - denotes hierarchy levels of organisation.
Count of employees- under level 3 there are this many employees and their count.
Skills- employees has their skills..
My question, I want to bring totals employees of table 1 to table 2.
I have attached my expected output picture.
Can I know , how it can be achieved?
Hi,
As you have a common column in both tables, then in Power Query you could merge the table and create a separate table :
1- Select Merge Queries from Home/Merge Queries/Merge queries as a new : (or you could merge in existing table) :
2- Select the master table in the first list and child in second, and click on the 'Level 3' and 'Level 3 Group' in each table, then click 'Ok':
3- now you have a new table combination of both tables, you could choose the column(s) from the list (by clicking on the icon in the last column in the new table) :
4- delete 'Skills' column by right click on the column and selecting Remove
5- After Applying it to Power BI, now you have a table :
Hi AtchayaP ,
I created another sample pbix file(see attachment), please check if that is what you want. You can create a measure as below to get it:
Count of employees from Table 1 = CALCULATE(DISTINCTCOUNT('Table 1'[Employee name]))Best Regards
10 Replies
MahyarTFMemorable Member
Hi,
As you have a common column in both tables, then in Power Query you could merge the table and create a separate table :
1- Select Merge Queries from Home/Merge Queries/Merge queries as a new : (or you could merge in existing table) :
2- Select the master table in the first list and child in second, and click on the 'Level 3' and 'Level 3 Group' in each table, then click 'Ok':
3- now you have a new table combination of both tables, you could choose the column(s) from the list (by clicking on the icon in the last column in the new table) :
4- delete 'Skills' column by right click on the column and selecting Remove
5- After Applying it to Power BI, now you have a table :
AtchayaPThe 3rd step you mentioned here:Now you have a new table combination of both tables, you could choose the column(s) from the list (by clicking on the icon in the last column in the new table)----- which one should I select
- MahyarTF
As you need the count, just select the 'Count ....'
Hi AtchayaP ，
I created a sample pbix file(see attachment) for you, please check whether that is what you want.
Method1: use DAX
You can create a calculated column as below:
Count of skill employees = CALCULATE ( SUM ( 'Table 1'[Count of employees] ), FILTER ( 'Table 1', 'Table 1'[Level 3] = 'Table 2'[Level 3 groups] ) )Method2: merge Table 1 and Table 2 as suggested by MahyarTF
let Source = Table.NestedJoin(#"Table 1", {"Level 3"}, #"Table 2", {"Level 3 groups"}, "Table 2", JoinKind.RightOuter), #"Expanded Table 2" = Table.ExpandTableColumn(Source, "Table 2", {"Count of employees", "Development status"}, {"Count of employees(development)", "Development status"}) in #"Expanded Table 2"Best Regards
- AtchayaP
These are my tables, table 1 about employees, Table 2--- skills- > employees have multiple skills,
table 3---Development item---> Focussed to do in future.
Table 1
Employee name
arjun
rithik
edvid
tharun
elsa
jose
Table 2
Employee name
skills
arjun
Python
arjun
data
arjun
excel
rithik
Program
rithik
java
edvid
Chemistry
edvid
,physics,
edvid
,maths,
edvid
biology
tharun
javascript
Table 3
Employee name
Development items
arjun
Prepare my team
tharun
Training
elsa
mentorship
jose
Workflow
jose
create model
My expected result is, that I want to bring the count of employees as 6 (table 1) to table 3. Can I know?
Expected Output.
Employee name
Development items(count)
Count of employees from table 1
arjun
Prepare my team
tharun
Training
elsa
mentorship
jose
Workflow
jose
create model
Totals
5
6
Hi AtchayaP ,
I created another sample pbix file(see attachment), please check if that is what you want. You can create a measure as below to get it:
Count of employees from Table 1 = CALCULATE(DISTINCTCOUNT('Table 1'[Employee name]))Best Regards
