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

[jacbob]

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:

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

[v-jiascu-msft]

Hi @jacbob,

Could you please mark the proper answer as a solution?

Best Regards,
Dale

[jacbob]

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

[v-jiascu-msft]

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

[jacbob]

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 · Accepted solution]

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.

Best Regards,

Dale