Showing results for 
Search instead for 
Did you mean: 
Rosscomatic Frequent Visitor
Frequent Visitor

Manipulating data from an excel spreadsheet

Hi all


I have a whole bunch of Excel files that are stored on a SharePoint Online Teamsite. I can access these files in PowerBI and expand them out but I'm having trouble manipulating them afterwards.


But the files have a lot of good info spread all over the place. I can't change how the files are produced either so this format is fixed.


Basically, I'd like to take the:

- Name of the courese (In this case "System X - 10 Coolest Features" but it varies)

- Date: (in this case 9/04/2019 but again this varies)

- Time: (in this case 02:00pm to 02:30pm but again this varies)


and put them in their own column appended to the end, next to Attended


Then delete these rows so that the obvious headings become the actual headings in Power BI


Does that all make sense?


See attached for a screen shot of one of my Excel files.

Capture.PNGScreen shot of one of my Excel files


Thanks in advance for your help!



Community Support Team
Community Support Team

Re: Manipulating data from an excel spreadsheet

Hi @Rosscomatic ,

If I understand your question correctly that you want to do some data format changes in query editor when you get data from SharePoint Online?

If so, please share your desired format so that I could understand your desired output better and help further on it.

Best Regards,



Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Rosscomatic Frequent Visitor
Frequent Visitor

Re: Manipulating data from an excel spreadsheet

Yes that sounds right. The final result would look like the attached. Except in PowerBI, not Excel if that makes sense...


So a table with the headings:

Attendee WOPID, Attendee name, Department, Date Registered, Attended, Course Name, Course Date, Course Time
Capture2.PNGFInal format that I'd like

Rosscomatic Frequent Visitor
Frequent Visitor

Re: Manipulating data from an excel spreadsheet

Any updates on this by any chance? @v-piga-msft 


Thanks heaps!

Helpful resources

Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 506 members 4,175 guests
Please welcome our newest community members: