Forum Discussion

KellyLen's avatar
KellyLen
Helper III
8 years ago
Solved

Problems with creating some custom columns

Hello,   I have a problem creating some columns.   Firstly, I would like to create column, which tells me that if there is "blank" in column Scanner ID and value "Basware Matching" in column Last...
  • MFelix's avatar
    MFelix
    8 years ago

    Try to use this:

     

    1st approver =
IF (
     'Table'[Scanned] = ""
        && 'Table'[Last Approver] = "Basware Matching",
    "1st pass mactching",
    BLANK ()
)
  • MFelix's avatar
    MFelix
    8 years ago

    Hi KellyLen,

     

    Try this formula:

    PO =
IF (
    'Table'[Company code] = "FI24"
        && 'Table'[Purchase Category (ext)] = "Technical Maintenance"
        && 'Table'[Purchase Sub Category] <> "Logistics"
        && (
            LEFT ( 'Table'[Gift Card/Traffic Type/Job No], 1 ) IN { "F", "P" }
                || LEFT ( 'Table'[Gift Card/Traffic Type/Job No], 2 ) IN { "BF" }
        ),
    "PO",
    BLANK ()
)

    Should give the result below:

     

     

    Regards,

    MFelix

KellyLen's avatar
KellyLen
Helper III
8 years ago

Thanks a lot, this worked! :) 

 

How about the other problem? :)

 

Best regards,

Kelly 

MFelix's avatar
MFelix
Super User
8 years ago

That was the easy question :D:D

 

Can you supply any mockup of your data?

 

 

Regards,

MFelix 

  • KellyLen's avatar
    KellyLen
    Helper III
    8 years ago

    :D 

     

    I do not know, how could I share my data with you? I can only share a screenshot of it:

     

     

     Kelly

     

    • MFelix's avatar
      MFelix
      Super User
      8 years ago

      Hi KellyLen,

       

      Try this formula:

      PO =
IF (
    'Table'[Company code] = "FI24"
        && 'Table'[Purchase Category (ext)] = "Technical Maintenance"
        && 'Table'[Purchase Sub Category] <> "Logistics"
        && (
            LEFT ( 'Table'[Gift Card/Traffic Type/Job No], 1 ) IN { "F", "P" }
                || LEFT ( 'Table'[Gift Card/Traffic Type/Job No], 2 ) IN { "BF" }
        ),
    "PO",
    BLANK ()
)

      Should give the result below:

       

       

      Regards,

      MFelix

      • KellyLen's avatar
        KellyLen
        Helper III
        8 years ago

        Thank you very much! It worked.

         

        Now I have another difficulty to combine two columns. I firstly created a conditional column in Query Editor: 

         

        Table.AddColumn(#"Replaced Value", "PO/ No PO", each if Text.StartsWith([Document header text], "14F") then "PO" else if Text.EndsWith([Document header text], "JFK") then "PO" else if [Document type] = "YC" then "PO" else if [Document type] = "YO" then "PO" else "No PO")

         

        I would like to get these conditions to your given formula as well so all these would end up in one column as values PO or No PO. 

         

        Are you able to help me with this as well? :) 

         

        Best regards, 

        Kelly