Poorly structure excel table/data

Anonymous · ‎01-20-2021

Hi All,

Context: Working with a Financial Services business that wants to analyse trades completed on a daily basis.

The data is captured manually in excel and is not well structured.

My concern is the client doesn't want to completely reengineer his reporting process but wants to view the data via Power BI Web App. The project will not entail just connecting to an Excel spreadsheet to slice and dice the data via Power BI interface.

How would you proceed?

Regards

Anonymous · ‎01-21-2021

@Anonymous

All we can do is to provide some ideas on how to optimize you table, please give a sample that describes your data table.

Paul Zheng _ Community Support Team

mahoneypat · ‎01-20-2021

I agree. Post some representative mock data (insert a table and paste it, or give Google Drive, OneDrive link). Much is possible in the query editor.

Pat

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.

@mahoneypa HoosierBI on YouTube

Rigensis · ‎01-20-2021

Hi,

There are multiple options on working with the data in Power Query Editor mode. Depending on how messy the excel data you mention is, it can be possible to filter, pivot and adjust it so that it is usable for your reporting needs.

Otherwise you could load the excel data into a database (if such is available to you for the specific use case), clean up the data there and use that as the source for your reports.

Without actually seeing the structure of the excel file in question there isn't really a way to give a more specific answer than that.