How can I create a PivotTable with multiple rows?

A Pivot Table has different layouts. Typically the "compact" view is something that goes beyond just a pivot table and goes into the visualization aspect. Power Query matches the "tabular view" layout that a pivot table can have in Excel.

To accomplish this, all you need to do is select the Status column and then do the Pivot transformation. In the Pivot dialog, select the advanced option and use the "Value column" as 'Account' and do a 'Count (all)' aggregation. 

That will yield the result that you're expecting, except that the "grand total" is something that you need to calculate yourself. We do recommend using other tools, such as pivot tables or Power BI, for analytical purposes as Power Query is primarily aimed to be a Data Preparation tool.

Below is the documentation on how to pivot columns in Power Query:

Pivot columns - Power Query | Microsoft Learn