Forum Discussion

CannoK's avatar
CannoK
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_...
Need Help
  • BA_Pete's avatar
    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's avatar
    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's avatar
    Anonymous
    4 years ago

    You sure can, like

     

    try Bron otherwise try AlternativeBron otherwise try thisOtherSource otherwise LastSource

     

    ---Nate

  • CannoK's avatar
    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's avatar
      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

      • CannoK's avatar
        CannoK
        New Member
        4 years ago

        Hi BA_Pete ,

         

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