Multi table lookup and count

[Ucubia]

Hi

I have two tables Hosts, and Applications

"Hosts" contains a list of unique server platforms being used, for the purposes of this thread the range being Server001-Server100
"Applications" contains a list of applications and the platform they reside upon, therefore there could be a single application upon a dedicated server, or there could be multiple applications sharing a server - for example

Application001 Server001
Application002 Server001
Application002 Server002
Application003 Server003
Application004 Server004
Application005 Server004
Application006 Server004

Ideally, want to add a column to identify the number of application each server to associated to, thus the output being:

Server001 2
Server002 1
Server003 1
Server004 3

I am stuck and struggling to understanding the easiest method to calculate the information when using multiple tables - I thought COUNTROWS might be the answer but to get the count would essentially mean I have to filter for each individual server which for the example would be challenging 1-100 but in reality the host list runs into multiple '00s

Any help gratefully received.

Many thanks, Ian

 

[alansidman]

Use Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Column2"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

Book2
	A	B	C	D	E
1	Column1	Column2		Column2	Count
2	Application001	Server001		Server001	2
3	Application002	Server001		Server002	1
4	Application002	Server002		Server003	1
5	Application003	Server003		Server004	3
6	Application004	Server004
7	Application005	Server004
8	Application006	Server004
Sheet1

Alternatively if you are using O365 Beta Insider then you can use GroupBy function

=GROUPBY(Table1[Column2],Table1[Column2],COUNTA)

 

[Ucubia]

Hi Alan

Thank you for your reply

Would this work within Power BI ? I mentioned earlier the customer is using Power BI as the Hosts table is Excel and the Application table is SQL

Thanks

 

[alansidman]

Yes. You will need to open PQ in PBI. I don't work with PBI, but my understanding it should be easy to do except I don't know the exact steps to open PQ.

 

[JEC]

@Ucubia
PBI is built on PowerQuery. Right click on the imported table to edit the query.

Or you could add a new table with DAX (same output)

Grouped_Table = SUMMARIZE(Tabel,[Column2], "Count", COUNT(Tabel[Column2]))

 

[Ucubia]

thanks Alan

@Ucubia
PBI is built on PowerQuery. Right click on the imported table to edit the query.

Or you could add a new table with DAX (same output)

Grouped_Table = SUMMARIZE(Tabel,[Column2], "Count", COUNT(Tabel[Column2]))

View attachment 107839

thank you JEC - i will give this a try