How to get a total sum of information from multiple columns

[Carter_364]

Hi there,

I am working with a similar data set to the image below, however on a much bigger scale (13,000+ rows).

I am trying to get a count for the following headings:

Total number of people who have lived in Wellington

Total number of people who have lived in Auckland

Total number of people who have lived in Christchurch

Total number of people who have lived in Dunedin

Total number of people who have lived in Rotorua

As well as slicing by year of birth (how many people born in 1992 have lived in each location, how many people born in 1993 have lived in each location etc.) if possible?

E.g. 'X' number of people have lived in Wellington, 'X' of which were born in 1992, 'X' of which were born in 1993 etc.

Is there a way to read all 4 location columns to find this information? A measure? Pivoting table?

[ryan_mayu · Accepted solution]

you can try to unpiovt the table in the PQ. select the location columns and unpivot columns. 

https://support.microsoft.com/en-us/office/unpivot-columns-power-query-0f7bad4b-9ea1-49c1-9d95-f5882...

then all the location data will be in the one column, that will be easier to calculate

if this does not solve the your problem, pls provide the sample data (not the screenshot) and the expected output based on the sample data you provided.

[Carter_364]

Perfect! Thank you very much!

[ryan_mayu]

you are welcome