Microsoft Power Query for Excel enhances self-service business intelligence (BI) for Excel with an intuitive and consistent experience for discovering, combining, and refining data across a wide variety of sources including relational, structured and semi-structured, OData, Web, Hadoop, Azure Marketplace, and more. Power Query also provides you with the ability to search for public data from sources such as Wikipedia.
Questions tagged [powerquery]
5627 questions
12
votes
5 answers
Auto-updating Power Query Connection via VBA
I have a Power Query set in myexcel.xlsx. I set its connections's properties as
this and this.
I wrote a VBA code like the following
Sub UpdateData()
Dim filename As String
Dim wbResults As Workbook
filename = "C:\myexcel.xlsx"
Set…
Salim
- 121
- 1
- 1
- 4
11
votes
1 answer
Excel: require Power Query add-in load from VBA macro
The scenario is a Windows Server 2012 R2, 64 bit; Excel 2010, 32 bit. Many users, just a couple of them have administrative privileges. I installed Power Query from the built-in administrator. Without asking anything, the add-in got installed for…
Enrico
- 325
- 1
- 2
- 15
10
votes
1 answer
Changing Excel Power Query connection string in C#
In an Excel Power Query file the data connection can be from a SQL server. We have a large number of files that specify a SQL server by name and this server is going to be decommissioned. We need to update the connection to replace the older server…
ivcubr
- 1,988
- 9
- 20
- 28
10
votes
1 answer
How to set a max run time / timeout for Power Query?
Is there a way to set a max run time or a timeout factor in Power Query so that after a specified period the query would terminate itself regardless of whether it was successfully executed or not?
This is important for me because I have built…
J Henkinson
- 133
- 1
- 1
- 8
10
votes
1 answer
How to get paginated data from API in Power BI
Let's say we have this endpoint https://reqres.in/api/users.
The response is
{
"page": 1,
"per_page": 3,
"total": 12,
"total_pages": 4,
"data": [
{
"id": 1,
"first_name": "George",
…
David
- 4,785
- 7
- 39
- 63
10
votes
2 answers
Replace all error values of all columns after importing datas (while keeping the rows)
An Excel table as data source may contain error values (#NA, #DIV/0), which could disturbe later some steps during the transformation process in Power Query.
Depending of the following steps, we may get no output but an error. So how to handle this…
visu-l
- 424
- 1
- 5
- 15
9
votes
4 answers
Delete all queries in a workbook
I am trying to write a sub to delete all queries in a workbook. I have got the code below:
Dim CN As Variant
Dim qTable As QueryTable
For Each CN In ThisWorkbook.Connections
CN.Delete
Next CN
For Each qTable In Sheets("Property Extract…
Chris
- 182
- 1
- 1
- 9
9
votes
1 answer
Power query function optional arguments
How can I create a power query function with an optional argument pls?
I have tried various permutations of creating the function syntax, presently like this:
let
fnDateToFileNameString=(inFileName as text, inDate as date, optional inDateFormat…
gregn
- 1,260
- 1
- 14
- 25
8
votes
3 answers
Power Query, make http POST request with form data
I have a REST API, which accepts only POST requests with form data.
I know that in Power Query JSON requests are like this:
let
url = "https://example.com",
body = "{ ""first_param"": ""AAAAA"", ""second_param"": ""BBBBBB""}",
…
Mr.D
- 7,353
- 13
- 60
- 119
8
votes
4 answers
How to wait for a Power Query refresh to finish?
Setup:
Windows 7 (at work)
Windows 10 (at home)
Excel 2016 (build 4627 at work)
Excel 2016 (build 8730 at home)
Power Query is setup to import, append, and transform a folder of Excel Files. This step works.
After using any technique to wait for a…
neurojelly
- 83
- 1
- 1
- 5
8
votes
2 answers
Can't save Excel sheet after adding data if the workbook contains a Pivot table
We're trying to build a default Excel dashboard which our users can download. When downloading the Excel sheet, we want to populate a sheet in the file with their data.
We're using EPPlus for the manipulation of the Excel sheet.
In short, the…
Christian A. Rasmussen
- 250
- 3
- 17
8
votes
1 answer
How do I reference a field name which includes a slash in Power Query
In Excel 2016 Get & Transform ("Power Query") it appears to be totally valid to have a field (column) name containing a slash character. However, when I try to reference this column, I can't find any way to escape the slash to make the reference…
Matti Wens
- 740
- 6
- 24
8
votes
3 answers
Power Query column name as parameter
dear wizards)
I'm trying to create a search function where I could use input:
1. Table for search
2. Column of this Table in which search will run
3. Value to search in the column specified in 2
the function looks like this:
( mTbl as table,…
Sergiy Razumov
- 159
- 2
- 9
8
votes
3 answers
How to check whether Connection Refresh was successful
In Excel 2016 VBA, I'm refreshing several queries like this:
MyWorkbook.Connections(MyConnectionName).Refresh
After the code is done, and no errors are encountered, I see that the hourglass icons for most of the queries are still spinning for…
Greg Lovern
- 958
- 4
- 18
- 36
8
votes
5 answers
Is there an ISNUMBER() or ISTEXT() equivalent for Power Query?
I have a column with mixed types of Number and Text and am trying to separate them into different columns using an if ... then ... else conditional. Is there an ISNUMBER() or ISTEXT equivalent for power query?
will
- 329
- 1
- 4
- 12