Creating funnel charts from awkward data

[Anonymous]

Hi

I have a problem from the world of British Further Eduction(FE):

I've demonstrated how Funnel Charts could be useful in FE, from this sample data I created:

Giving a funnel chart like this:

Which is great BUT, unfortunately, in the real world the source data isn't in such a convenient format. Here are 2 sample files of more realistic data formats:

(a) in this format, the "Status" column shows which stage the applicant has achieved; (full file can be found at 

https://1drv.ms/x/s!AmxJyApgEAcYgo8NoDs8mx7lBf-Hxg)

(b) In this file, a "Y" indicates the status stated in the column header has been achieved (file is at https://1drv.ms/x/s!AmxJyApgEAcYgo8OZ7cZXMHgwEI70A)

Statuses are defined on the following file (at https://1drv.ms/x/s!AmxJyApgEAcYgo8PLoR2pPl2qJbUtg)

I've tried using Power BI, with the Funnel Chart desktop visualisation, to get a meaningful funnel chart (i.e. like the first one in this post that I generated from my own test data) from either real-world input file formats, without success - could anyone advise me how I might achieve this without resorting to VBA to reformat the input data?

Thanks for your help!

[v-cherch-msft · Accepted solution]

Hi @Anonymous

You may try to use 'Unpivot Only Selected Columns' in Query Editor as below. Then create relationships for the 3 sheets. Last, you may create measure or drag the columns to Funnel Visual as requested.

Regards,

Cherie

[Anonymous]

Thank you Cherie - easy when you know how, I've used my data to produce the following Funel Diagram: