Hi,
I'm new here and am having trouble seeing if this question has been raised before. I'm having trouble even describing what I am trying to do.
I am wanting to find a way to take a table which lists patients and every diagnosis they have with one diagnosis per line meaning that some patients have lots and lots of lines and transforming it to one line per patient with all the diagnoses listed from left to right after the patient details in the appropriate order for the diagnosis.
I have attached an example of what I mean. The table on the left is the starting data with the two grey columns either side added to enable me to transform the data into the table on the right. The initial data is first sorted into the correct order for the diagnoses for each patient and the Order column is added in indicate the correct order. the concat() column is then added at the far left of the table to enable a vlookup to work in the second table.
All suggestions gratefully accepted 🙂
Thanks you
Leeanne
Hi Leeanne,
It can be done, but I don't see any reason for this transformation, you have the data already in a proper tabular format. All you have to do is to add a pivot table that can help visualizing data in a different structure., More, you can sort the pivot by the columns you want.
In the file attached, I added a query to reformat your sample data and 2 pivot tables based on your original data.
Thank you Catalin. I haven't use Power Query yet but will definitely be able to use this.
