Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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),
Solved! Go to 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.
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),
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.
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.
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.
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),
@JollyRoger01 , I think you have to reverse of it
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |