Forum Discussion

New Member

4 years ago

Solved

If one source returns an error use other source

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.

Need Help

BA_Pete

4 years ago

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

4 years ago

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
4 years ago
You sure can, like

try Bron otherwise try AlternativeBron otherwise try thisOtherSource otherwise LastSource

---Nate

10 Replies

BA_Pete
Super User
4 years ago
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
  New Member
  4 years ago
  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
    Super User
    4 years ago
    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
Anonymous
Not applicable
4 years ago
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
  New Member
  4 years ago
  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
    Not applicable
    4 years ago
    You sure can, like
    
    try Bron otherwise try AlternativeBron otherwise try thisOtherSource otherwise LastSource
    
    ---Nate

Forum Discussion

If one source returns an error use other source

10 Replies

Related content

Data source

OData source error

Incremental Refresh on Dataflow sourced from SharePoint

Source option missing

Data Source Credentials