Reporting Help

[mbrady1973]

Hi, I was asked to create a report from a Sharepoint survey which is all well and good. The issue that I am having pertains to the way the data is structured and what is being asked.
I am pulling the data in using PowerQuery directly from Sharepoint. I have a link to a picture that shows the issue and the desired outcome.
As you will see in the first table it is structured in a way that makes it difficult for aggregating.
Does anyone have any idea how I can transform the data to look like table 2.
I'd really like to figure this out in the Sharepoint Survey but, I'm not skilled there at all.

https://www.dropbox.com/s/fd7n9p8tmy86pbn/Survey_forMrExcel.PNG?dl=0

 

[Matt Allington]

There are a few different ways to do this. What I recommend is first load the data into power query. Set this query so that it doesn't load. Then create a new query referencing the first query, right click and select reference. With this second query, delete the second completed by column, and rename the first completed by column to be simply "completed". Then start a third query by referencing the first query, delete the first completed column, and rename the second completed by column to also be "completed" Finally append queries two and three together.

If you want to learn how to use power query quickly and effectively, check out my online training Power Query for Excel and Power BI Online Training - Excelerator BI

 

[mbrady1973]

Matt...
Thank you so much. This method is perfect! Works perfect! I could NOT wrap my head around this but, now that you gave me a solution, it makes perfect sense.
Thanks again!