IF Statement to return on same row depending on condition

[DataLife321]

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 

[dufoq3]

Hi @DataLife321,

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

[DataLife321]

Customer	Column1	CompanySA	CompanyMS	CompanyFE	CustomColumn Expectation
Company1	Y	Y	Y	Y	CompanySA - CompanyFE - CompanyMS
Company2	Y	Y	Y	Y	CompanySA - CompanyMS
Company3	Y	Y	Y	Y

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

[v-xinruzhu-msft · Accepted solution]

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.

[DataLife321]

Great stuff mate thank you.. Managed to use this to get it working appreciate it 

[dufoq3]

@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.