Forum Discussion
If one source returns an error use other source
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 OutputPete
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 genericFinalPete
You sure can, like
try Bron otherwise try AlternativeBron otherwise try thisOtherSource otherwise LastSource
---Nate
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
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
Hi BA_Pete ,
That is correct. I'm in the process of giving this a try right now. Thank you for the help!
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 genericFinalPete