Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Question on Conditional Column as Parameter

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 kee...
Need Help
Tutorial Requests
mussaenda's avatar
mussaenda
Community Champion
6 years ago

Anonymous,

 

Thanks for the screenshots. Now I understand you better.

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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Here's a link to download an example:

LINK  

  • mussaenda's avatar
    mussaenda
    Community Champion
    6 years ago

    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.

     

    NameDepartmentProject IPMA - AProject IPMA - BProject IPMA - CProject IPMA - DProject PMP Project ITSMProject ScrumProject Prince 2 - PrincessProject Prince 2 - Change Management
    MichaelDevelopment  Know of  Advanced Beginner  
    KevinConsultancyAdvancedIntermediateBeginnerKnow of   Know of  
    Michael 2Development  Know of  Advanced Intermediate  
    Kevin 2ConsultancyKnow ofBeginnerIntermediateAdvanced  Advanced  
    Michael 3Development  Expert Intermediate Beginner  
    Kevin 3ConsultancyIntermediateBeginnerAdvancedAdvanced  Expert  

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        mussaenda
        Community Champion
        6 years ago

         

        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!