Analyse monitoring form companies into power bi

[Leicester City Fox]

Hi All

Good Morning

I have been asked to analyse Quarter monitoring form companies into power bi

The monitoring forms are in excel format see example attached for Q1, and the other tabs would have Q2, Q3, Q4. the monitoring form format does not change for each quarter.

I am new to power bi, but can anybody come up with the easiest way to analyse the result in each box on the monitor in power bi and compare each quarter of the forms?

I just want to know if this is doable or what the easiest work round to achieve this or away to put the data in different format in excel so power bi can analyse the result and compare each quarter info.

See Example Form attached.


I would be grateful for any ideas and the simpler the better, I thank you for your thoughts on this matter I am honest I don’t know where to start.


Many Thanks

The Leicester Fox

 

[Chihiro]

Damn, this is terrible format for ETL process to handle.

Merged ranges intermixed at various locations, and structure isn't consistent through column... different metric overlap in column(s) and same metric is spread in multiple column... etc. This is going to be a major headache for you to manage.

As well, form has no relationship between each section (or lost due to aggregation of underlying data) and gives no insight into data as is.
Ex:
There's no insight you can garner from Demographic data. What % of White Brits are in which age groups etc. Nothing ties each section to the other.

Isn't there raw/source data for this form? I'd recommend gathering data from that source and using that in PowerBI.
With current sample, you might as well, just display the form as is.

 

[Leicester City Fox]

Damn, this is terrible format for ETL process to handle.

Merged ranges intermixed at various locations, and structure isn't consistent through column... different metric overlap in column(s) and same metric is spread in multiple column... etc. This is going to be a major headache for you to manage.

As well, form has no relationship between each section (or lost due to aggregation of underlying data) and gives no insight into data as is.
Ex:
There's no insight you can garner from Demographic data. What % of White Brits are in which age groups etc. Nothing ties each section to the other.

Isn't there raw/source data for this form? I'd recommend gathering data from that source and using that in PowerBI.
With current sample, you might as well, just display the form as is.

Hi Chihiro

Good Morning

Your right it’s a mine filed …. Yeah, the form is input manually by the companies …. Apart from putting this info in Colum in separate excel spread sheet and the linking it to power bi I was wonder if I could make power bi select the correct cells?

Anyway, back to the drawing board thanks for the reply

Leicester Fox

 

[Chihiro]

It's not impossible to transform data. Though I'd probably go with VBA rather than PowerQuery/BI in this instance.

However, you'd want raw data. Data as is, gives you no useful business intelligence.