How to interrupt query on logical error ? RRS feed

  • Question

  • How to interrupt query on a logical  error   and display a message ?

    Also why Power Query is singleton ?

    It does not allow to work in different workbooks in parallel

    and if we have copy an excel from another and an excel error happens,

    at first, it  loses all changes in Query, and in recovery, 

    it displays weird messages about the first workbook .

    Sorry I am very nervous after 3 days tests.

    In these 3 days  I succeeded to have an interface to our web services

    and I understand the tremendous potential of Power Query,

    but I am disappointed from the lack of essential functionality to help our work.

    Even  a function like   isNull  is not implemented , 

    or something like the string.Format  in order to set Json schema with  parameters and to communicate with Web Services.  ( Now, web  services are the main datasource)

    the way to read parameters from  cells, creating  one column-one row tables   is complete time and mind waste.

     Or  why  in a  sorted table if it is loaded both in worksheet and data model,  then sorting is missing in action.

    Or even in the user interface,  where formula line is  practically unusable and the  really helpful  Advanced Query Editor  is somewhere hidden in a  tab,  but also in this editor   the Undo   does not work, if you close  the Editor and return. So in order to test something, I  have to copy paste the relevant part of query steps, even as  precaution.

    A lot lot of issues.  Right now, I could make 30-40 questions for the basics.  And I have read the only one book for the subject.

    Hope, that it is my biased opinion.

    I am programmer and use MS development tools last 25 years.



    Wednesday, November 5, 2014 10:59 AM


All replies

  • You can return an error with error "error Text". Sample usage:

      Source = Lines.FromBinary(Web.Contents("")),
      Result = if List.Count(Source) < 100 then error "not enough rows" else Source{100}

    You can implement isNull as follows:

    isNull = (value, default) => if value <> null then value else default

    If I remember correctly, PowerPivot (aka "the data model") doesn't retain the input sort order -- so even if you sort the data in Power Query, it won't show up that way in PowerPivot. And when you load to both the data model and the sheet, we deliberately load the sheet from the data model so that loses sort order as well.

    I don't understand what you mean by singleton or some of your other points.

    Thursday, November 6, 2014 2:19 PM
  • my first question was

    "How to interrupt query on a logical  error   and display a message ?

    Do you think that your suggestion with the error,  interrupts the  query ?

    Maybe you are right, but I tried with the few that I understand and it didn't.

    I call one web service and then other services. If the result of the first service is not the correct, I don't want to waste time , resources, logic.

    2nd question about  null

    You suggestion is a custom user function correct?

    isNull = (value, default) => if value <> null then value else default

    Should I define it in every query or   there is a kind of library for reusable user functions ?

    If I should define it in every query, then it is not good for me, because we have a lot of such functions.

    This is the big problem that I need to avoid.

    3nd question

    I asked a function like   string.Format, in order to create, easily Json  text with dynamic parameters from cells.

    Relative to this question is  my remark that the way to get  query parameters from cells

    creating tables ,  is not productive at all.

    About Power Query as singleton.  If I have opened the Power query editor,  I cannot open another excel workbook,   or to switch in another already opened to work in parallel.

    When I want to copy code from  queries, this is unproductive.

    Also when I edit in the Advanced Query editor  (only viable solution for complex  queries)

    the Undo does not work, if you close the editor and return after the test. If I have changed the code  a lot , then  only solution is to copy text as precaution  or to put the old code  in comments.

    this is also unproductive.

    Other questions   later.

    Thursday, November 6, 2014 4:28 PM
  • Errors do not terminate directly; they propagate forward in a dataflow fashion. If there's an expression without a dependency on a previous expression that produced an error, it may well still get evaluated. So if A and B are independent, the result C depends on both A and B, and A produces an error -- B may still be evaluated. Under those circumstances, you will have to explicitly introduce a dependency to avoid evaluation of B.

    There is not currently any way to create a library of reusable functions, though this is something we'd very much like to add someday.

    Your request for better formatting support is noted. Is this mostly specific to JSON? If there were a "Json.ToText" function to mirror the existing "Json.Document", would that meet all or most of your needs?

    The usual term for what you describe about the Power Query editor window is "modal".

    Currently, the easiest way to get all of the code from a workbook is to use "Send a Frown" from the Power Query ribbon and then extract it from the generated email. Unfortunately, this loses your formatting.

    In general, the Advanced Editor could benefit from a lot of improvement: better support for indentation, undo, syntax highlighting, intellisense, etc. We'd like to do all of these some day, but they don't have a very high priority right now. Until then, our recommendation is to do exactly what you describe: keep another editor open to store the text and paste it into the PQ Advanced Editor as needed.

    Thursday, November 6, 2014 4:49 PM
  • As I understand, it is not possible to interrupt it. When  you say to "introduce a dependency to avoid evaluation of B. ". Any example ?  I think it is very difficult and it will make practically unusable, an already complex query.

    I don't want to make things more complex. I think, at least, for  resource consumed tasks, as web services or datasources, we  should have an easy way to cancel their step. 

    The string.format is not specific to Json.  I need it, in order to construct   the json request schema with parameters  and then to POST it as content  in a request  to web service  with json format.  As there is not an easy way to access parameters, as I mentioned before,  there is a need to create the json POST request  to web service manually.

    I would like to be able to transform  a record   to Json schema.

    Yes the   usual term for the editor window is "modal",   my English is somehow poor, but I have noticed something else also, weird. If I copy workbooks with queries and then an excel file has damage, it influences and the original. Maybe I have not understood well.  But it happened to me, I lost a lot of code, in both excel copies and from that moment, I don't copy workbooks, I create blank queries and copy paste code.  For this reason I thought that the Power Query is a kind of singleton, a unique process instance. Maybe I was wrong.



    p.m I wish to had now the new Power BI with Query in browser.

    Thursday, November 6, 2014 5:12 PM
  • yes, what  I need is  a Json.ToText  ( the  Json.strignify)

    or/and  to convert  the record To Binary or Text.

    Is it possible ?

    here is my problem

    JsonRequestSchema="{ ""Start"":null ,""End"": null  }",

     JsonRequest.Start=Text.From(DateTime.Date(Excel.CurrentWorkbook(){[Name="dtStart"]}[Content]{0}[Start]) ),
    Headers=[#"Content-Type"="application/json; charset=utf-8"]   

    I get an errorExpression.Error: Parameter is expected to be of type Text.Type or Binary.Type.

    any solution ?


    Thursday, November 6, 2014 8:03 PM
  • This is not functionality which currently exists, I'm afraid, though I'm considering adding it in the next few months.
    Thursday, November 6, 2014 10:38 PM