Headings with multiple columns

Question

Hi, are you able to help me solve this problem of sorting this data in a power query please? I wish to analyse the data associated with each heading under the words "Route Hazards" however there are several rows to merge and several columns with the same headings for data relating to individual rows (see Reference ID).

		Route hazards	Route hazards	Route hazards
		Poor sight lines or visibility of oncoming traffic problems on this route are adequately controlled?	Drop off or pick up locations and bus stops, including school sites, have sufficient space for the safe loading and unloading of passengers?	There is a safe approach distance (at least 26.0m) between parked vehicles or other structures and the Bus Stop Flag.
Reference Id	Date Started (Computer Generated)	Answer	Score	Comment	Attachments	Answer	Score	Comment	Actions	Answer	Score	Comment
HAZ0508	2024-07-03	• Yes	2	No Issues found		• Yes	2	No Issues found		• Yes	2	No Issues found
HAZ0509	2024-07-03	• Yes	2	No Issues found		• No	0	Stop 5 (PM) School Rd bushes need pruning on side of road.	1	• Yes	2	No Issues found

Answer 1

Sample file changed.

Open the file

Open the queries pane
https://support.microsoft.com/en-us/office/manage-queries-power-query-76f93a6d-37d9-46b5-bc40-d5f2162401f5

Edit the Import query

Select the Source step and change the path to the file on your PC:

Close the PQ editor

Data \ Refresh All

Andreas.

Answer 2

Sample file:
https://www.dropbox.com/scl/fi/wps00eaf9z5gb6ltdywm8/81fee302-7199-4612-a425-5f2707ba9621.xlsx?rlkey=iygabf49iwvkcd89it4ryruwh&st=z5fazo0o&dl=1

Answer 3

Hi Andreas,

Thanks again, I was not successful so I have attached all the data and blanked out names. I guess I can replace that data. I would very much appreciate your help once again. I hope this link works.

Headings with multiple columns.xlsx

Thank you.

Answer 4

Copy the data into my file into the Data tab.

Expand the named range "Data" to include the new data.

Click Data \ Refresh all

If that doesn't work you have to edit the query and adjust the steps.

If you need further help, I need to see your file.
Why a sample file is important for troubleshooting. How to do it. - Microsoft Community

Andreas.

Answer 5

HI Andreas,

Thankyou so much it looks great, however can you help me a little more please because there are more columns and rows attached to the original worksheet. How can I add this data please?

Many thanks