Powerbi

[AtchayaP]

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?

[MahyarTF · Accepted solution]

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 :

[v-yiruan-msft]

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

[v-yiruan-msft · Accepted solution]

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

[MahyarTF · Accepted solution]

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 :

[AtchayaP]

The 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 ....'

[AtchayaP]

To merge queries, is that column should be common ?

Because I have some differences in in table 1 and table 2

[MahyarTF]

Hi, As we want to create a relation between two tables and bring the rest column(s) based on these columns, should have a common value. this relation is same as the condition between two table in Sql query

[AtchayaP]

Ok , once i apply this doing in my table will let you know

[danextian]

Hi @AtchayaP ,

Please  add a data source that  we anyone  can easily copy-paste (not an image).

How to Get Your Question Answered Quickly