cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
jacbob Frequent Visitor
Frequent Visitor

Recommendations on how to handle excel data that is not well formed for ingest

Hello, 

 

My finance person has a nice excel spreadsheet and I would like to pull that data into power bi. However, the data is not well formed for ingest. Here is an example:

pbi example.png

 

 

 

I would like to know if anyone has suggestions on how to handle this data. The Previous month cell and Notes information is important, but I can't figure out how to cleanly extract that data. And I would like to avoid having my finance person rearrange their spreadsheets.

 

Thanks,

-cj

1 ACCEPTED SOLUTION

Accepted Solutions
v-jiascu-msft Super Contributor
Super Contributor

Re: Recommendations on how to handle excel data that is not well formed for ingest

Hi @jacbob,

 

I think it's good to have a format like below. Please try out the demo in the attachment. These steps are all applied in the Query Editor.

 

how to.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards,

Dale

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

View solution in original post

5 REPLIES 5
v-jiascu-msft Super Contributor
Super Contributor

Re: Recommendations on how to handle excel data that is not well formed for ingest

Hi @jacbob,

 

Are all the data the same format? Same numbers of rows and columns. Separate by one blank row. Maybe you can format it using VBA before loading or format it using R after loading.

 

Best Regards,

Dale

 

 

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

Re: Recommendations on how to handle excel data that is not well formed for ingest

Hi Dale,

 

Yes, this is the correct format, with a blank row in between and another piece of data in line with other separate data. I thinking of creating a new sheet in the excel that references these one off cells and putting them neatly into rows/columns for better power bi ingest.

 

v-jiascu-msft Super Contributor
Super Contributor

Re: Recommendations on how to handle excel data that is not well formed for ingest

Hi @jacbob,

 

I think it's good to have a format like below. Please try out the demo in the attachment. These steps are all applied in the Query Editor.

 

how to.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards,

Dale

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

View solution in original post

v-jiascu-msft Super Contributor
Super Contributor

Re: Recommendations on how to handle excel data that is not well formed for ingest

Hi @jacbob,

 

Could you please mark the proper answer as a solution?

 

Best Regards,
Dale

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

Re: Recommendations on how to handle excel data that is not well formed for ingest

I just did. Thank you very much for your help.

Helpful resources

Announcements
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: 203 members 2,503 guests
Please welcome our newest community members: