grouping 2 columns to see totals in pivot chart

I work for a city and I need to track collisions to spot problem areas. I have a table that has a column for the reported (primary street) and a column for an intersecting (secondary street). Depending on how its entered by police officers, Main St. could end up in either column. So if I have 5 collisions say Main street & 3rd, Main & Walter, Walter & MLK, MLK & Main, 3rd & Main; I would like the pivot table to List Main St once with total of 4 instances, 3rd = 2, Walter = 2, MLK =2, etc.

Also is there a way to do a pivot table that groups by intersection no matter which street is primary? So it would list Main Street & 3rd and would have 2 instances.

Sorry if this is the wrong place or if it's been answered before, I have no clue what to search because everything I've thought of to search doesn't come close to what I'm needing. Thanks

Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file.

Using Power Query which is called Get and Transform in your version of Excel, here is the Mcode to get the results shown in the attached file

Please Login or Register  to view this content.

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.