Forum Discussion

DataLife321's avatar
DataLife321
Frequent Visitor
2 years ago
Solved

IF Statement to return on same row depending on condition

Hi All, 

Can someone please help to see if this is possible in the Power Query Custom Column, I am trying to get the result to show in the same row but not sure how to add a concatenation &"-"& so when we return the results the value will show this for Company Name1

 

Expected Output for Company Name1 in the Custom Colum= 
"This is marked Yes due to no record in Column3" - "This is marked Yes due to no record in Column4" 

I want to then continue the script to look at other Company Names which I have tried below -

 

if[ColumnName1] = "y"
and [ColumnName2] = "Company1" and
[ColumnName3] <> ""
then "This is marked Yes due to no record in Column3 for Company 1" (I would expect something like &" - "& around this section)
else
if [ColumnName1] = "y" and
[ColumnName2] = "Company1" and [ColumnName4 <> ""
then "This is marked Yes for yes due to no record in Column4 "
else

 

(This will then look at other company names)

 

if [ColumnName1] = "y" and
[ColumnName2] = "Company2" and [ColumnName56 <> ""
then "This is marked Yes due to no record in Column56 for Company2"

else null

 

 




I hope that makes sense 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi DataLife321 

    You can try the following code in advanced editor in power query.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PLUjMqzRU0lGKRMGxOnBJIzRJZDljZHE0fSbI4mhypsjiYLlYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, Column1 = _t, CompanySA = _t, CompanyMS = _t, CompanyFE = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer", type text}, {"Column1", type text}, {"CompanySA", type text}, {"CompanyMS", type text}, {"CompanyFE", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let 
a=if [CompanySA]="Y" then "CompanySA" else "",
b=if [CompanyMS]="Y" then "CompanyMS" else "",
c=if [CompanyFE]="Y" then "CompanyFE" else ""
in if [Customer]="Company1"and [Column1]="Y" and a<>"" then a&" "&b&" "&c else if [Customer]<>"Company1"and [Column1]="Y" and a<>"" then a&" "&b else null)
in
    #"Added Custom"

    Output

    Best Regards!

    Yolo Zhu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

5 Replies

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    Hi DataLife321,

     

    I'd like to help you but provide sample data (as table) and also expected result please.

    • DataLife321's avatar
      DataLife321
      Frequent Visitor
      2 years ago
      CustomerColumn1CompanySACompanyMSCompanyFECustomColumn Expectation
      Company1YYYYCompanySA - CompanyFE - CompanyMS
      Company2YYYYCompanySA - CompanyMS
      Company3YYYY 

       

      Expected Result should be shown above in the table -  (I did this in a new column column measure in the Data view but prefer a custom column as I need more IF's)  Happy to keep going if you can provide me a way to do it this way. 

       

      DAXColumn111 = IF(

          'Master Sheet - (Doc)'[Column1] = "y" &&'Master Sheet - (Doc)'[Customer] = "Company1" &&'Master Sheet - ( Doc)'[CompanySA] <> BLANK(),"CompanySA"    & " " &

          IF(
          'Master Sheet - (Doc)'[Column1] = "y" &&'Master Sheet - (Doc)'[Customer] = "Company1" &&'Master Sheet - (Doc)'[CompanyMS] <> BLANK(),"CompanyMS" & " " &
          IF(
          'Master Sheet - (Doc)'[Column1] = "y" &&'Master Sheet - (Doc)'[Customer] = "Company1" &&'Master Sheet - (Doc)'[CompanyFE] <> BLANK(),"CompanyFE",
       
      I want to now add a new IF to look at other company Names to see if they have the Column 1 selected and Company SA+MS fields <> blank then they should return Column Name.. 
       
           IF(
          'Master Sheet - (Doc)'[Column1] = "y" &&'Master Sheet - (Doc)'[Customer] = "Company2" &&'Master Sheet - (Doc)'[CompanySA] <> BLANK(),"CompanySA" & " " &
          IF(
          'Master Sheet - (Doc)'[Column1] = "y" &&'Master Sheet - (Doc)'[Customer] = "Company2" &&'Master Sheet - (Doc)'[CompanyMS] <> BLANK(),"CompanyMS"
       
      I hope that helps
      • dufoq3's avatar
        dufoq3
        Community Champion
        2 years ago

        DataLife321, I'm sorry but I don't get the logic.

         

        How you can achieve this if all column values are the same and there is no connection between Customer (Company1, Company2 and Company3) vs CompanySA, CompanyMS and CompanyFE

         

         

        Are you sure that you provided correct sample data?

        Try to explan as simple as possible please.