Chart creation from Survey Data

[Chitemerere]

I have the following data coming from a survey:

How can i create a chart which clearly shows which countries are "ISO 17025 Certified" and which countries are "WHO prequalified".

Thanking you in advance.

Chris

[FrankAT · Accepted solution]

Hi @Chitemerere ,

one part of the solution with Power Query, the other with DAX and conditional formatting.

You can download my solution pbix-file until  ...

One part of the solution with Power Query, the other with DAX and conditional fomatting

Regards FrankAT

[Chitemerere]

Thank you very much, most appreciated.  Would be kind enough to explain the Power Query Process?

Regards,

Chris

[FrankAT]

Hi @Chitemerere ,

here is my explanation of the Power Query part:

1. Duplicating the NQCLStatus column.
2. Adding a custom column: How long is the string in column NQCLStatus?
3. Replaced the substring "ISO 17025" with "" inside the NQCLSTatus column. If the string contains this substring the original textlength is now shorter.
4. Adding a custom column: How long is the shortened NQCLStatus string?
5. In the duplicated column NQCLStatus - Copy I replaced the substring "WHO".
6. Adding a custom column: How long is the shortened NQCLStatus string in column NQCLStatus - Copy. Same as 3..
7. Adding conditional column ISO 17025 Certified: If the TextLengthFull <> NewTextLengthISO then 1 otherwise 0.
8. Adding conditional column WHO prequalified: If the TextLengthFull <> NewTextLengthWHO then 1 otherwise 0.
9. Removed unnecessary columns.
10. Changed data type.

That's it.

Regards FrankAT

[Chitemerere]

Hi, would you mind redacting country names from the solution tables in the community site?

Regards,

Chris

[Chitemerere]

Many thanks, got it.

Regards,

Chris