Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

if else with or and is not working

Hi,
   I have two columns
       1) Labour class
       2) HRMS
i am using the following m query code attached in picture but it is not giving correct result.
First 3 conditions are giving correct result but fourth one is not correct.

4th condition is bringing foreman too i dont want to see foreman. what is wrong with that. is there any syntax problem?
i am using text.start because foreman value is not separate.

 

 

Need Help
Show and Tell

6 Replies

  • HotChilli's avatar
    HotChilli
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Can you provide sample data or a picture which shows the problem please?  

    We need to see 3 columns and the right/wrong values which are being generated so that we can debug

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      I applied the condition where labour class is 1A and 1B but it is showing 1A and F9 in labour class colum.
      and in HRMS colum it is showing Foreman while i applied HRMS not in Foreman.
      Note:- there are two value of foreman one is capital and second one is in small letter.

      • ppm1's avatar
        ppm1
        Icon for Solution Sage rankSolution Sage
        3 years ago

        FYI that Power Query is case sensitive. You can uppercase your column and then use all uppercase for the text values in your if. Or you can use Text.Upper or Text.Lower within your expression.

         

        Pat

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Icon for Community Champion rankCommunity Champion
    3 years ago

    = List.Skip({"Foreman","Senior Foreman","Supervisor"},each not Text.StartsWith([HRMS],_,Comparer.OrdinalIgnoreCase)){0}? ??if List.Contains({"1AA","1bb"},[labour Calss]) then "Junior Staff" else null)

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi wdx223_Daniel  it is giving error on if as i have attached SS.


      • wdx223_Daniel's avatar
        wdx223_Daniel
        Icon for Community Champion rankCommunity Champion
        3 years ago

        = List.Skip({"Foreman","Senior Foreman","Supervisor"},each not Text.StartsWith([HRMS],_,Comparer.OrdinalIgnoreCase)){0}? ??(if List.Contains({"1AA","1bb"},[labour Calss]) then "Junior Staff" else null))