Log In
Register
Home
February 1, 2019
Offline
Hi,
i have 2 workbooks. From first one i am connecting it to second one (From SourceTable.xlsx to PowerQUeryResult.xlsx).
I want user to refresh PQ query using button (not refresh on start- i supose there is option in PQ for this?).
If SourceTable.xlsx exists in the same folder - refresh data. If not - keep table empty (put only "(blank)" in first row).
It can be build in pivot or usual table - no matter.
How to solve the issue?
Please help,
Jacek
See Answer
July 16, 2010
Offline
Hi Jacek,
Power Query doesn't have an option to refresh under the conditions you describe. This is a job for VBA. I'll move this post to the VBA forum.
Mynda
February 1, 2019
Offline
Hi Mynda,
thank you very much.
in PQ there is an option to handle errors using Try function. But i do not know how to implement it. The topic is not for VBA.
I have 2 possible options:
1. Just do not refresh table if data is not there - leave table empty and do not show error.
I tried with:
let
Source = try (Excel.Workbook(File.Contents("C:\Users\admin\Desktop\Newest Pull request\PQ example\SourceTable.xlsx"), null, true)) otherwise null,
Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"Name", type text}, {"Surname", type text}})
in
#"Changed Type"
but i am getting that M is not returning list, only null.
So how to not display error when user is refreshing PQ tables?
Or how to input nulls into table when error is raised?
2. Create button in VBA to choose path where folder is. If file is not present, do not refresh tables.
Best,
Jacek
February 1, 2019
Offline
Or i can create empty row in Power Query somehow like here:
// create an empty record
EmptyRecord =
let
FieldNames = Record.FieldNames(FirstRecord),
EmptyValues = List.Repeat({""},Record.FieldCount(FirstRecord))
in
Record.FromList(EmptyValues,FieldNames),
If source file is not there add blank row to the table using Try.
Please help with implementation,
Jacek
July 16, 2010
Offline
AFAIK refresh is not controlled from within the query. It either refreshes or it doesn't, in order for the query to 'see' if the file is there it needs to refresh the query and then it's too late because the refresh has already taken place.
I'll ask Catalin if he's aware of any way to do this in PQ, but my gut feel is that this isn't possible. You need VBA to check the file, then either refresh or not.
Mynda
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Hi Jacek,
Try this:
let
Source = Excel.Workbook(File.Contents("C:\Users\Catalin\Desktop\New folder\SourceTable.xlsx"), null, true),
Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"Name", type text}, {"Surname", type text}})
in
#"Changed Type"
otherwise #table({"Name", "Surname"}, {})
If source file is not found, blank table with 2 columns is created: #table({"Name", "Surname"}, {})
Answers Post
1 Guest(s)
