Alert user to PQ Errors

Power Query
1

I have a workbook with 20+ Queries. The users of the workbook are novices and so I don’t want them to be concerned with knowing about PQ.

How do I alert them that there is an error in one or more Query and/or the refresh didn’t successfully complete. Is it possible to output a list to an Error Tab that they could refer to a support person to have the errors addressed?

The attached workbook has three queries for illustration only. One of the queries refers to an external source and so it should fail.

Open to ideas

Kind Regards

Allister

pqeRRORaLERT.xlsx (20.9 KB)

2

One way: you set up the way you need the workbook/query data setup so you can produce the report.

I have done this many times when i was using excel at budget time.

They soon realize if not setup properly the report will not be produced.

3

Thanks Keith

That would be great - but my users don’t follow the rules and without an alert they will be unaware that the Refresh etc failed and that the info they see is not correct.

So I thought I would alert them that there is a problem - and then I could tell them if it was of them making or not.

Kind Regards

Allister

4

Hi @AllisterB,

You might find this an interesting read.

I hope this is helpful

1 Like
5

Hi @AllisterB, did the response provided by @Melissa help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

6

Hi @AllisterB, we’ve noticed that no response has been received from you since the 7th of April. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

7

Hi @AllisterB, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.

We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!

8

The article below was very helpful

I have now got this to work on one query. In general terms How would I go about doing it for all queries in a workbook so I get one table with all the queries in the workbook detailed

Thank you

Allister

9

Hi @AllisterB,

To illustrate, this catches and reports on errors in two queries.
Adjust to your needs.

Error reporting.pbix (19.8 KB)

I hope this is helpful.

10

Thank You Melissa

I have attached a simple setup for this. The attached setup includes the code you posted
The solution you kindly supplied I think assumes the Sources have thesame structure. I need a solution that can handle different columns. is this possible ?

Thank You

Allister

Multiple Queries2.xlsx (21.4 KB)

11

Hi @AllisterB,

No it doesn’t assume nor requires the same structure. See attached.
Multiple Queries2.xlsx (21.7 KB)

I hope this is helpful

12

Thank You Melissa.
I see how you have done this :grinning:
At the moment one needs to manually manually enter the Names of the Queries in the wbk and also determine and then enter all the unique column names of those queries.

Is there a M code that will populate the Queries on the Source Line and the Column names on the Expandtemp line for me? This would then mean I can use the Error Query with other workbooks and when adding or deleting queries I wold not have to maintain the Error Query.

Thank you

13

Hi @AllisterB,

Alternatively use Excel.CurrentWorkbook()
You can’t extract the query name of course but you can get the Table name.

Hope this helps

Multiple Queries v2.xlsx (22.3 KB)