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.
Screen shot of one of my Excel files
Thanks in advance for your help!
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.
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 TimeFInal format that I'd like
Any updates on this by any chance? @v-piga-msft
Power BI Super User, Greg Deckler, explains
Register by September 5 to save $200
Engage and empower students with Power BI!
Continue your learning in our online communities.