Complex Reformatting of Cells to Columns

[Anonymous]

I have been struggling with a way to easily import information into a report I run monthly.  Right now I pull the information manually but would like to automate as much as I can. 

The difficult part of this report is that it comes formated to look nice... and not structured for easy importing. This is the only way I can currently get the report and I have reached out for alternatives, right now this is all I got

After I clean up the blank column I can widdle the information down to..

 

My Overall goal is to move the cells that feature the agent and time information to its own column next to their metrics.

Below is an example of how I would like to move the information.  I don't know if PowerBi/Dax has an option to do this.  Any ideas are welcomed.

 

[Lind25 · Accepted solution]

You will need to use the Query Editor / Power Query / M to tidy up your data.
Add a new column using Extract to return the value after the delimiter Agent: .
Then use fill down to copy this to each row.

[Anonymous]

I do not have Excel 2016 or Office 365 for business,  I will not be able to use Power Query 😞
I do appreciate the reply though.

[Lind25]

Power Query is part of Power BI (its the Query Editor button) so you are able to use it.

As a side point, Power Query is a free Excel addin for 2013 and 2010.

[Anonymous]

Thank you so much!  I will be looking into both.

[v-yulgu-msft]

Hi @Anonymous,

Have you worked it out? If so, would you please kindly mark the helpful reply as an answer so that it can benefit more users?

Regards,

Yuliana Gu