If one source returns an error use other source

[CannoK]

Hi everyone, 

I'm fairly new to using M code and this problem is giving me headaches. I have the following code:

let
Bron = Table.NestedJoin(#"csv rabo", {"Rekening_ID"}, dSleutels, {"Rekening_ID"}, "dSleutels", JoinKind.LeftOuter),
#"dSleutels uitgevouwen" = Table.ExpandTableColumn(Bron, "dSleutels", {"Tegenpartij", "Sleutel", "OG_ID", "GBR_ID", "E_AFK", "Sub_categorie"}, {"Tegenpartij", "Sleutel", "OG_ID", "GBR_ID", "E_AFK", "Sub_categorie"}),
#"Aangepaste kolom toegevoegd" = Table.AddColumn(#"dSleutels uitgevouwen", "Sleutel test", each Text.Contains([Omschrijving],[Sleutel])),
#"Rijen gefilterd" = Table.SelectRows(#"Aangepaste kolom toegevoegd", each ([Sleutel test] = true)),
#"Dubbele waarden verwijderd" = Table.Distinct(#"Rijen gefilterd", {"Index"}),
#"Kolommen verwijderd" = Table.RemoveColumns(#"Dubbele waarden verwijderd",{"Sleutel", "Sleutel test"})
in
#"Kolommen verwijderd"

Now I have 1 source here (Bron) but I would like to add some if statements that checks if the source returns an error and if so to use another source. 

For example my source right now is #"csv rabo", but sometimes I want it to be #"csv ing" or #"csv abn" depending on which one of the three doesn't return an error and is actually available to process. The layout of the three connections is the same, so the formatting after the source can stay the same. 

It's just at the moment of importing the source I want the code to make a decision which one of the three doesn't return an error and use that one.

I've been looking around for a long time now and I can't make it work. Perhaps someone here can help me get on the right track. 

Thanks in advance. 

Edit: One piece of information I should add is that the original source is a web.contents connection, I'm using a sharepoint location for a csv file. Perhaps that is of importance. 

[BA_Pete · Accepted solution]

Hi @CannoK ,

Not sure what you mean by "it's not following up with any of the other steps". Your code doesn't tell PQ to perform any steps if Bron2 is used, except for the AlternativeExpand step.

You will need to restructure your query a bit. It should be something like this:

let

Bron1 = Bron1source,
Xforms1 = All transformation steps to do if Bron1 is source,
Final1 = The final output step using Bron1,

Bron2 = Bron2source,
Xforms2 = All transformation steps to do if Bron2 is source,
Final2 = The final output step using Bron2,

TestForError = try Final1,
Output = if TestForError[HasError] then Final2 else Final1

in
Output

Pete

[BA_Pete · Accepted solution]

@CannoK ,

Cool, glad it looks like it will work for you.

I think this can be slightly optimised if you are performing exactly the same transformations whether using Bron1 or Bron2, with a structure like this:

let

Bron1 = Bron1source,
Xform1 = First transformation step to do if Bron1 is source,

Bron2 = Bron2source,
Xform2 = First transformation step to do if Bron2 is source,

TestForError = try Xform1,
Output = if TestForError[HasError] then Xform2 else Xform1

genericXforms = steps to perform on either source, using Output step as first argument in first function,
genericFinal = Final step of generic xforms

in
genericFinal

Pete

[Anonymous]

Why not just do the test right up front and then just carry on, like

let Bron = ...,

AlternativeBron = ...,

GoodSource = try Bron otherwise AlternativeBron,

NextSteps = the rest of the M code

--Nate

[CannoK]

Hi @Anonymous , 

I see how that could work maybe even better yes. Is it possible to do multiple otherwise statements? I'm trying to make it work for 3 or more datasources.

[Anonymous · Accepted solution]

You sure can, like

try Bron otherwise try AlternativeBron otherwise try thisOtherSource otherwise LastSource

---Nate

[CannoK]

**bleep** that is cool. It works like a charm. Thanks a lot man. 

[BA_Pete]

Hi @CannoK ,

Check out this blog from Chris Webb where he goes through almost exactly what you're talking about, but will need a tiny bit of editing to include the third potential source.

Make sure to read the comments too, as there's usually great improvements on the initial process from other readers.

https://blog.crossjoin.co.uk/2014/09/18/handling-data-source-errors-in-power-query/ 

Pete

[CannoK]

Hi @BA_Pete ,

Thank you for your response. I've been looking at the link you provided and it looks promising. I've modified my code to be the following:

let
Bron = Table.NestedJoin(#"csv rabo", {"Rekening_ID"}, dSleutels, {"Rekening_ID"}, "dSleutels", JoinKind.LeftOuter),

#"dSleutels uitgevouwen" = Table.ExpandTableColumn(Bron, "dSleutels", {"Tegenpartij", "Sleutel", "OG_ID", "GBR_ID", "E_AFK", "Sub_categorie"}, {"Tegenpartij", "Sleutel", "OG_ID", "GBR_ID", "E_AFK", "Sub_categorie"}),
#"Aangepaste kolom toegevoegd" = Table.AddColumn(#"dSleutels uitgevouwen", "Sleutel test", each Text.Contains([Omschrijving],[Sleutel])),
#"Rijen gefilterd" = Table.SelectRows(#"Aangepaste kolom toegevoegd", each ([Sleutel test] = true)),
#"Dubbele waarden verwijderd" = Table.Distinct(#"Rijen gefilterd", {"Index"}),
#"Kolommen verwijderd" = Table.RemoveColumns(#"Dubbele waarden verwijderd",{"Sleutel", "Sleutel test"}),

AlternativeBron = Table.NestedJoin(#"csv ing betaal", {"Rekening_ID"}, dSleutels, {"Rekening_ID"}, "dSleutels", JoinKind.LeftOuter,
{{"Error","Error",0}}),

TestForError= try Bron,

Output = if TestForError[HasError] then AlternativeBron else #"Kolommen verwijderd"
in
Output

It seems to catch that an error is going on, however the output doesn't work. I get the following error message: 

Expression.Error: We cannot convert a value of type List to type Function

I'm sure I'm doing something wrong, but not sure what.

Edit: So I changed the code a little bit. Now it is expanding the second source, so that's great. However it's not following up with any of the other steps. It only expands the different source. Any idea how I might change that? 

let
Bron1 = Table.NestedJoin(#"csv rabo", {"Rekening_ID"}, dSleutels, {"Rekening_ID"}, "dSleutels", JoinKind.LeftOuter),
Bron2 = Table.NestedJoin(#"csv ing betaal", {"Rekening_ID"}, dSleutels, {"Rekening_ID"}, "dSleutels", JoinKind.LeftOuter),

#"dSleutels uitgevouwen" = Table.ExpandTableColumn(Bron1, "dSleutels", {"Tegenpartij", "Sleutel", "OG_ID", "GBR_ID", "E_AFK", "Sub_categorie"}, {"Tegenpartij", "Sleutel", "OG_ID", "GBR_ID", "E_AFK", "Sub_categorie"}),
#"Aangepaste kolom toegevoegd" = Table.AddColumn(#"dSleutels uitgevouwen", "Sleutel test", each Text.Contains([Omschrijving],[Sleutel])),
#"Rijen gefilterd" = Table.SelectRows(#"Aangepaste kolom toegevoegd", each ([Sleutel test] = true)),
#"Dubbele waarden verwijderd" = Table.Distinct(#"Rijen gefilterd", {"Index"}),
#"Kolommen verwijderd" = Table.RemoveColumns(#"Dubbele waarden verwijderd",{"Sleutel", "Sleutel test"}),

AlternativeExpand = Table.ExpandTableColumn(Bron2, "dSleutels", {"Tegenpartij", "Sleutel", "OG_ID", "GBR_ID", "E_AFK", "Sub_categorie"}, {"Tegenpartij", "Sleutel", "OG_ID", "GBR_ID", "E_AFK", "Sub_categorie"}),
TestForError= try Bron1,
Output = if TestForError[HasError] then AlternativeExpand else #"Kolommen verwijderd"

in
Output

[BA_Pete · Accepted solution]

Hi @CannoK ,

Not sure what you mean by "it's not following up with any of the other steps". Your code doesn't tell PQ to perform any steps if Bron2 is used, except for the AlternativeExpand step.

You will need to restructure your query a bit. It should be something like this:

let

Bron1 = Bron1source,
Xforms1 = All transformation steps to do if Bron1 is source,
Final1 = The final output step using Bron1,

Bron2 = Bron2source,
Xforms2 = All transformation steps to do if Bron2 is source,
Final2 = The final output step using Bron2,

TestForError = try Final1,
Output = if TestForError[HasError] then Final2 else Final1

in
Output

Pete

[CannoK]

Hi @BA_Pete ,

That is correct. I'm in the process of giving this a try right now. Thank you for the help! 

[BA_Pete · Accepted solution]

@CannoK ,

Cool, glad it looks like it will work for you.

I think this can be slightly optimised if you are performing exactly the same transformations whether using Bron1 or Bron2, with a structure like this:

let

Bron1 = Bron1source,
Xform1 = First transformation step to do if Bron1 is source,

Bron2 = Bron2source,
Xform2 = First transformation step to do if Bron2 is source,

TestForError = try Xform1,
Output = if TestForError[HasError] then Xform2 else Xform1

genericXforms = steps to perform on either source, using Output step as first argument in first function,
genericFinal = Final step of generic xforms

in
genericFinal

Pete

[CannoK]

@BA_Pete ,

You're amazing! That really did the trick. Thanks for taking the time to explain the logic behind the steps for me. I've got it optimized now exactly using that structure. Thanks again.