Creating a table like a pivot table

Since, you don't want to go through a pivot table, you can download following mock-up solution.

https://1drv.ms/x/s!Akd5y6ruJhvhuRVRl0cdM3wUZaN6?e=sEB3dt

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Account"}, {{"Temp", each _, type table [Account=text, #"HQ-NHQ"=text]}}),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 0, 1, Int64.Type),
    #"Expanded Temp" = Table.ExpandTableColumn(#"Added Index", "Temp", {"HQ-NHQ"}, {"HQ-NHQ"}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Expanded Temp", "Index", "Index - Copy"),
    #"Pivoted Column" = Table.Pivot(#"Duplicated Column", List.Distinct(#"Duplicated Column"[#"HQ-NHQ"]), "HQ-NHQ", "Index", List.Count),
    #"Sorted Rows" = Table.Sort(#"Pivoted Column",{{"Index - Copy", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index - Copy"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Duplicate HQ and NHQ", each if [HQ]>0 and [NHQ]>0 then "Duplicate" else "Unique"),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Duplicate NHQ", each if [NHQ]>1 then "Duplicate" else "Unique"),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"HQ", "NHQ"})
in
    #"Removed Columns1"