Help converting IFERROR Excel formulas to PowerQuery M

[s_schwantes]

I have a dataset in Excel formatted as a table. I need to add 4 new columns to this table to test some logic. I have the following four Excel formulas in blue font, which I'd like to translate into M and Power Query in Power BI:

column 1 - "Doc ID in PMT? (Y/N) (quick check)" = IFERROR(IF(MATCH([@Document id]],[dataT_PMT.Document Id],0),"Yes","No"),"No")

column 2 - "Intake IDs of Doc IDs in PMT (Y Only)" = IF([@Doc ID in PMT? (Y/N) (quick check)]] = "Yes", [@[Intake id]], "")

column 3 - "Intake IDs of Doc IDs NOT in PMT (N Only)" = IF([@Doc ID in PMT?(Y/N) (quick check)]] = "No", [@[Intake id]], "")

column 4 - "Review Realized? (Intake ID represented in PMT) (Y/N) (Full Check)" = IFERROR(IF(MATCH([@[Intake IDs of Doc IDs in PMT (N Only)]], [Intake IDs of Doc IDs in PMT (Y Only)],0),"Yes"),"No")

Of course IFERROR does not work in M. I've tinkered with Try but haven't quite figured out how to implement all the logic above from Excel. Any guidance would be greatly appreciated!

Thanks!

Steve

[v-stephen-msft]

Hi @s_schwantes ,

use "try....otherwise".

try <some code hear that may result in an error> otherwise  <run this code in case of error>

note that try and otherwise are NOT capitalized.  Also, no commas are needed.

Best Regards,

Stephen Tao

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

[Vera_33]

Hi @s_schwantes 

Can you provide some sample data in a format which we can copy? try...otherwise... is the structure, but it depends on what you want, provide some data then we can see if you need it

[s_schwantes]

Hi Vera_33,

Sorry strict company regulations prohibit me from posting any thing other than what I am able to type into the forum. Can't even upload a JPEG or provide link to other cloud storage; eg., Box, DropBox etc. (not accessible).

Thanks,

Steve

[Vera_33 · Accepted solution]

Hi @s_schwantes 

I am not asking you to post anything sensitive, you can't even give some dummy data like this? All the columns you mentioned are in the same table? I don't see why you need all 4 columns...maybe my dummy data can't represent your table

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRU0lECYSOlWJ1oJSMjMBPGNTYGMiE4NhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Intake id" = _t, ID = _t, #"dataT_PMT.Document Id" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "column1", each if List.Contains(Source[dataT_PMT.Document Id],[ID]) then "Yes" else "No"),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "column2", each if [column1]="Yes" then [Intake id] else ""),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "column3", each if [column1]="No" then [Intake id] else ""),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "column4", each if List.Contains(#"Added Custom2"[column2],[column3]) then "Yes" else "No")
in
    #"Added Custom3"