Question on Conditional Column as Parameter

[Anonymous]

I am working on a skillset survey, and the rating is know of, beginner, intermediate, advanced and expert, and I have used a conditional column to assign values 1, 2, 3, 4, and 5. I would like to keep my text in my visual presentation of the data, so I would like to avoid using replace value.

It is quite an extensive survey, so I would like my definition set in my conditional column to include all coloumns, but I am stuck on this. Do I need to set up a parametre instead? If so, I would be very grateful for any help 🙂

Thank you so much!

[mussaenda]

hi @Anonymous 

I didn't get what you really need.

Do you want to show 1 as beginner, 2 as intermediate and so on?

[Anonymous]

Hi Mussaenda,

Yes 🙂 I've added some screenshots to my previous reply. I hope it might explain better.

[mussaenda]

@Anonymous,

Thanks for the screenshots. Now I understand you better.

But please provide a sample data that can be copied. Thanks

[Anonymous]

Here's a link to download an example:

LINK  

[mussaenda]

@Anonymous,

I have attached the pbix link from what I understood from your explanation.

Hope this helps.

https://gofile.io/?c=4Eaksj

BTW, I have changed your sample data just to see differences.

Name	Department	Project IPMA - A	Project IPMA - B	Project IPMA - C	Project IPMA - D	Project PMP	Project ITSM	Project Scrum	Project Prince 2 - Princess	Project Prince 2 - Change Management
Michael	Development			Know of		Advanced		Beginner
Kevin	Consultancy	Advanced	Intermediate	Beginner	Know of			Know of
Michael 2	Development			Know of		Advanced		Intermediate
Kevin 2	Consultancy	Know of	Beginner	Intermediate	Advanced			Advanced
Michael 3	Development			Expert		Intermediate		Beginner
Kevin 3	Consultancy	Intermediate	Beginner	Advanced	Advanced			Expert

[Anonymous]

That is exactly what I was hoping to do 😄

Can I perhaps trouble you to explain me how to set up the query for future use?

Thank you very much!

[mussaenda]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ndBNDsIgEAXgqzSsu9IT+LcwjSdouiDlqSR0aCqi3l7SoDKkbFywmIR578u0rTjp/iphRC328DB2HEAuTFV8DdlHZc+fcaO8pB4qjltcNBGm70JXt6KB1xSmnaXb3bjw/8U3j+QwDVBaOvCQrC0BsIaIrlZ/sjNARp9jOf6HSLBZStaxUMvx6yL+8BwxubK1fPU5lNPLx050C3aGCBXdGw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, Department = _t, #"Project IPMA - A" = _t, #"Project IPMA - B" = _t, #"Project IPMA - C" = _t, #"Project IPMA - D" = _t, #"Project PMP " = _t, #"Project ITSM" = _t, #"Project Scrum" = _t, #"Project Prince 2 - Princess" = _t, #"Project Prince 2 - Change Management" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Department", type text}, {"Project IPMA - A", type text}, {"Project IPMA - B", type text}, {"Project IPMA - C", type text}, {"Project IPMA - D", type text}, {"Project PMP ", type text}, {"Project ITSM", type any}, {"Project Scrum", type text}, {"Project Prince 2 - Princess", type any}, {"Project Prince 2 - Change Management", type any}}),
    #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type", {"Project IPMA - A", "Project IPMA - B", "Project IPMA - C", "Project IPMA - D", "Project PMP ", "Project ITSM", "Project Scrum", "Project Prince 2 - Princess", "Project Prince 2 - Change Management"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Only Selected Columns",{{"Attribute", "Project"}}),
    #"Uppercased Text" = Table.TransformColumns(#"Renamed Columns",{{"Value", Text.Upper, type text}}),
    #"Added Conditional Column" = Table.AddColumn(#"Uppercased Text", "Rank", each if Text.Contains([Value], "KNOW OF") then 1 else if Text.Contains([Value], "BEGINNER") then 2 else if Text.Contains([Value], "INTERMEDIATE") then 3 else if Text.Contains([Value], "ADVANCED") then 4 else if Text.Contains([Value], "EXPERT") then 5 else null),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Conditional Column",{{"Rank", Int64.Type}})
in
    #"Changed Type1"

@Anonymous,

Copy this query and paste to the advanced editor in Power Query so you can see the step by step queries

And just drag again the fields according to your needs.

Enjoy your Power BI journey!

[v-lionel-msft]

Hi @Anonymous ,

“so I would like my definition set in my conditional column to include all coloumns,”

I don't particularly understand your needs, could you show me a sample data model and describe your needs in detail.

Best Regards,
Lionel Chen

[Anonymous]

Thank you for your replies 🙂

This is my data as I have been sorting it in Excel. I am working on creating graph example and it is important that rating is not changed to a numerical value. So I have created a conditional column to assign numerical value to the text, but my question whether I would need to create a conditional column for each column in my spreadsheet, or is there someway smarter to do it?