cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
Highlighted
Community Support Team
Community Support Team

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.
5 REPLIES 5
Community Support Team
Community Support Team

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.

 

Highlighted
Community Support Team
Community Support Team

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.
Community Support Team
Community Support Team

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.