Design question to trigger PQ results (without VBA?)

[JackDanIce]

Hi,

I have the following set up but unsure how to create a suitable interface for the user

I have data tables, loaded as queries into PQ and subsequent steps to filter and return results.

Main Sheet
The user enters a string into A1
In B1 a dropdown returns all partial matches to A1 (against a reference list) and the user selects a value from the dropdown
The query then uses the value in B1 to return a table of results into an Output sheet (set to overwrite all previous results in the table)

Is there a way to trigger the query to refresh, without using Data->Queries & Connections -> Refresh All and without using a Worksheet_Change event?

Separately what step can I add to my query to cope with null results (there's a single case where this occurs for any searched string in the dropdown list in B1)
e.g. If the query returns no results, how can I clear the output table or return an empty table?

TIA,
Jack

 

[GraH]

Hi Jack,
I don't know about many other options.
Avoiding VBA and a shorter way to refresh might be to simply use a right mouse click on the table an selecting refresh from the context menu. Every query this table is dependant on will be refreshed. So no need to force a refresh all.

Your second questions puzzles me. How is PQ not returning a blank table when there is no data to return? I have that all the time and seems to me a default behaviour. What did you do?

 

[JackDanIce]

Hey G,

Thanks for reply again! The output table isn't on the main sheet so trying to avoid unnecessary user movements.

To be honest, the VBA isn't much (trigger an action if a specific cell changes) so will use it - it was an effort to keep file type xlsx.


You kindly helped with my last post on slowness, by suggesting a separate function for local currency type and turning off save to data model. Together, both made huge performance gains! Power Query returning results very slowly

In same file, first query is Account User which returns a value to filter for.

There is one (edge) case where this value will not be found, so Output Data needs to return nothing and clear the output table.

Instead, PQ crashes and error is shown in step where I filter a column for this value and a null value isn't found.

Does this explain the problem any clearer? If not, I'll update and repost file in the mentioned link with updated changes.

Thank you,
Jack

 

[GraH]

Aha, I see now. Hmm, maybe turning the main transformation query into a function may work. Then using try transformation function otherwise table.FromRecords(), where you create a record with all field names, the columns headers, equal to null.
I'm using this technique more and more the days.

 

[smozgur]

Aha, I see now. Hmm, maybe turning the main transformation query into a function may work. Then using try transformation function otherwise table.FromRecords(), where you create a record with all field names, the columns headers, equal to null.
I'm using this technique more and more the days.

I agree with @GraH.

I do something like this when I work with web APIs. I check the variable (identifier) which is supposed to contain data, and if it is null or has no rows (Table.IsEmpty function), then I create a new table with necessary columns and no corresponding records. If it has data, then I just return the table.

    CheckEmpty =
        if TableName is null or Table.IsEmpty(TableName) then
            #table(type table[Column1 = any, Column2 = any],{})
        else
            TableName

Then I use the CheckEmpty variable as the table in the next step.

 

[GraH]

Cool stuff @smozgur and a shorter way of making the table.

 

[smozgur]

Cool stuff @smozgur and a shorter way of making the table.

Thanks, @GraH. I am really amazed with M language endless tricks.