Use column value if column value does NOT contain numbers

[JollyRoger01]

I have the following code that I would like to add an if statement to the end of. I would like to check a column called "CAGE Code". If the column "CAGE Code" does NOT contain a number, then make the current column (Manufacturer) equal "CAGE Code".

Could anyone help me out with this?

    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Manufacturer", each if [#"Haystack Matching CAGE - ALL.Company1"] <> null then [#"Haystack Matching CAGE - ALL.Company1"] else
if [#"Haystack Matching PN & CAGE - ALL.CAGE Code"] <> null then [#"Haystack Matching PN & CAGE - ALL.CAGE Code"] else if 
null),

[Anonymous · Accepted solution]

Assume the following are variables...

Step2Table = #"Added Custom2"
Step3Table = #"Added Custom3"
NewFieldName = "Manufacturer"
ManufactuerFieldName = [#"Haystack Matching CAGE - ALL.Company1"]
CageCodeFieldName = [#"Haystack Matching PN & CAGE - ALL.CAGE Code"]

Functions:

Table.AddColumn(<ToWhichTable>,<WhatIsTheNewFieldName>,each <LogicToGenerateTheValueForEachRow>)
IF <condition> THEN <evaluate if the condition is true> ELSE <evaluate if the condition is false>

Pseuodocode (based on the power query posted by you on your first post)

Step3Table = 
	Table.AddColumn(
		Step2Table,
		NewFieldName,
		each
			if ManufacturerFieldName is not null 
			then use ManufactuerFieldName 
			else
				if CageCodeFieldName is not null
				then use CageCodeFieldName
				else
					if <...> 

<...> Complete the logic in the last line, I will send you the ready-to-paste power query by replacing the variable names with actual field names, functions, and syntaxes.

Be clear on the following part:

Case 1:

Cage Code field can be alphanumeric . ABC123 and you want to check if "ABC123" has any number like "123" or

Case 2:

The cage code field is either alphabetical or numerical. i.e. in some rows, it can have ABC and in some other rows it can have 123 and you want to determine whether it is "ABC" or "123" as opposed to checking if a number is there inside "ABC123" and "ABCDEF"

These 2 cases are different things and accordingly, the logic will vary.

[Anonymous]

Can you try something like this... Change the field names and table names as necessary.

 AddColumn1 = 
        Table.AddColumn(
            #"Added Custom2",
            "CAGE Code2",
            each 
                let 
                   x = try Number.FromText(Record.Field(_,"CAGE Code")) 
                in 
                   if x[HasError] then Record.Field(_,"Manufacturer") else Record.Field(_,"CAGE Code")
            )
in
    AddColumn1

The following is the logic.

CAGE Code can have numbers or text.

We are trying to convert the cage code to a number to see if it succeeds or fails. If it is a number, it will succeed (no errors) otherwise it fails (errors)

Then we add a new column, if the text to number conversion results in an error (meaning CAGE does not have number), then we use Manufacturer in that field else use CAGE code in that field.

Disclaimer: I have not worked extensively on Power Query. This solution is just as good as my skill sets in power query.

[JollyRoger01]

Thank you for your response. Unfortuntely I have no experience with Power Query or code in general, so I don't know how to use this directly.

I need to include the two if statements I included in my code as well as the end else statement and have all the column names exactly the same. Do you know how I include this in your code (so I can copy and paste it)? My document takes about 45 minutes to run when I make a change now, so I am not able to try possible solutions and troubleshoot - I need to paste in the final code.

[Anonymous · Accepted solution]

Assume the following are variables...

Step2Table = #"Added Custom2"
Step3Table = #"Added Custom3"
NewFieldName = "Manufacturer"
ManufactuerFieldName = [#"Haystack Matching CAGE - ALL.Company1"]
CageCodeFieldName = [#"Haystack Matching PN & CAGE - ALL.CAGE Code"]

Functions:

Table.AddColumn(<ToWhichTable>,<WhatIsTheNewFieldName>,each <LogicToGenerateTheValueForEachRow>)
IF <condition> THEN <evaluate if the condition is true> ELSE <evaluate if the condition is false>

Pseuodocode (based on the power query posted by you on your first post)

Step3Table = 
	Table.AddColumn(
		Step2Table,
		NewFieldName,
		each
			if ManufacturerFieldName is not null 
			then use ManufactuerFieldName 
			else
				if CageCodeFieldName is not null
				then use CageCodeFieldName
				else
					if <...> 

<...> Complete the logic in the last line, I will send you the ready-to-paste power query by replacing the variable names with actual field names, functions, and syntaxes.

Be clear on the following part:

Case 1:

Cage Code field can be alphanumeric . ABC123 and you want to check if "ABC123" has any number like "123" or

Case 2:

The cage code field is either alphabetical or numerical. i.e. in some rows, it can have ABC and in some other rows it can have 123 and you want to determine whether it is "ABC" or "123" as opposed to checking if a number is there inside "ABC123" and "ABCDEF"

These 2 cases are different things and accordingly, the logic will vary.

[JollyRoger01 · Accepted solution]

Thank you for your detailed response. A Freelancer provided the following copy and paste solution:

#"Added Custom3" = Table.AddColumn(#"Added Custom2", "Manufacturer", each if [#"Haystack Matching CAGE - ALL.Company1"] <> null then [#"Haystack Matching CAGE - ALL.Company1"] else if [#"Haystack Matching PN & CAGE - ALL.CAGE Code"] <> null then [#"Haystack Matching PN & CAGE - ALL.CAGE Code"] else if Text.Length(Text.Select([CAGE Code], {"0".."9"})) = 0 then [CAGE Code] else null),

[amitchandak]

@JollyRoger01 , I think you have to reverse of it

https://eriksvensen.wordpress.com/2018/03/06/extraction-of-number-or-text-from-a-column-with-both-te...