Create New Table with Count

[smithshire]

I have a table with the following information:-

BDM	Depot
Internal01	Liverpool
Internal01	Liverpool
Internal01	Manchester
Internal01	Manchester
Internal02	Manchester
Internal03	Liverpool
Internal03	Manchester
Internal03	Manchester
Internal03	Bolton
Internal03	Bolton

I wish to create a new table that counts the number of times each BDM is listed against each depot and adds in the current date each day the table is updated:-

Date	Depot	BDM	Total
21/04/2022	Liverpool	Internal01	2
21/04/2022	Manchester	Internal01	2
21/04/2022	Manchester	Internal02	1
21/04/2022	Liverpool	Internal03	1
21/04/2022	Manchester	Internal03	2
21/04/2022	Bolton	Internal03	2

What is the best way to approach this?

[johnt75 · Accepted solution]

To get the date when the data was last refreshed you would need to create a new query in Power Query which returns the current date. You can then use that to create a new summary table like

Summary Table = CROSSJOIN(
	SUMMARIZECOLUMNS( 'Table'[Depot], 'Table'[BDM], "Total", COUNTROWS('Table') ),
	LastUpdated
)

where LastUpdated is the table with the last updated date in it