34 questions
1
vote
1
answer
48
views
How to remove specific pattern in query excel?
I am using Microsoft 365. I have a query table in excel. There's one column I need to split it into 2 column by using number and dot and 1 space (1., 2., 3....) as an indicator where to split it. The ...
- 15
1
vote
1
answer
89
views
Maximum Date where products are bigger than zero and less than calculated date
I'm encountering an error while trying to create a custom column in Power Query in Excel. The goal is to implement two tests:
If the "PPL" column value for a row is zero, return the date ...
- 11
0
votes
1
answer
89
views
Table transformation in Excel file
I have an Excel file where I occasionally enter new information.
and this information looks like this:
Column1
Column2
Column3
Column4
Column5
Column6
Column7
Column8
Column9
1
Воробій Сергій ...
0
votes
3
answers
396
views
Add Date of Report to Column using Power Query - Excel
Not sure if this will make sense. I have been Googling and trying to find the answer to this but cannot work it out, I am trying to add in a custom formula to the Power Query that will add in the date ...
- 19
0
votes
1
answer
203
views
Excel, Power Query: Add multiple columns from query2 into query1
A continuation of my question here: Excel, query: How to add specific info from query 2 into query 1 with lookup
I have 2 queries, "Overview" and "Data". And I want to add the ...
- 87
0
votes
2
answers
148
views
Excel, query: How to add specific info from query 2 into query 1 with lookup
I want to add data from the second query (Data) to the first query (Overview) depending on the information in the first query. I'll show my question with pictures to make it more clear. I have these ...
- 87
0
votes
1
answer
143
views
Excel: How to prevent "Refresh All" from activating "Worksheet_Change" VBA trigger
I have a VBA trigger listening for user changes in the sheet. It is also a sheet that Queries a SQL Database and prints the information. When the user clicks "Refresh All", it also triggers ...
- 48
0
votes
1
answer
43
views
Web Query missing css elements in the table
I using a web query on the NCSL Excuse Absentee Voting Table (https://www.ncsl.org/elections-and-campaigns/table-2-excuses-to-vote-absentee). The table uses this element to mark cells that I would ...
- 3
0
votes
0
answers
179
views
VBA add Query via CSV with Formula let: replace value
I have this CSV:
columns: "Replace Column 1" and "Replace Column 2" need to be replaced "," to "."
in addition, i need to open the file with UTF-8 encoding.
...
- 169
1
vote
0
answers
380
views
Trouble handling Expression.Error: Column in Power Query
I use Power Query to bring the figures from 24 excel file that are stored in SharePoint in different folders, the files had the same structure, the sheets contains my data source hasn't "header&...
- 11
3
votes
5
answers
671
views
Make combinations using Excel queries
I need to make all the possible combinations(not permutations) with three numbers using excel queries.
I have six numbers in a column. From A1:A6. I need to make all the possible combinations(not ...
- 45
1
vote
1
answer
46
views
Excel Query (find all + value of another cell in relation to results)
I need to search ALL cells for a value (VALUE A) and then output the value of all cells 7 cells above that contains the value (VALUE A).
For example, if I search for V01 in the screenshot attached, I ...
- 11
0
votes
1
answer
3k
views
Lock Structured References to column names in Excel-formulas when data Query is updated and refreshed
I use references to other columns in my table with the following formula:
=TableName[@[ColumnHeaderName]]
And use the following as a Table array:
TableName[[#All];[ColumnHeaderName]] (this is used if ...
0
votes
1
answer
712
views
Refreshing Excel Queries with Task Scheduler
I am trying to automate Query refresh in MS Office Professional Plus 2016.
I have a cmd script which runs vbs script which runs Excel macro. Everything works if I run it manually. The problem occurs ...
user19232952
-1
votes
1
answer
154
views
Transofrm excel query to google sheets
I try to convert/transform excel query to google sheets.
This the code from excel:
let
Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
#"Changed Type" = Table....
- 37
0
votes
1
answer
2k
views
How to prompt the 'access web contact' popup window when opening excel file
I want to prompt the 'access web content' popup window when anyone opens my excel file, so people can use choose their own settings.
At the moment it isn't giving the option to chose your permissions -...
- 87
0
votes
1
answer
48
views
Why does my excel query return a stack overflow for some people and not others?
I have a power pivot excel dashboard with pivot charts. I obtain the data by using the jira REST API to the source, and the query is scheduled.
I have tested access to the dashboard with my colleagues....
- 87
1
vote
1
answer
259
views
How to import multiple csv files into querie connections in Excel?
I would like to import data in Excel from CSV files (they have the same format) using VBA to Loop data importing and formating with Queries. My first goal would be to create the connections from the ...
- 13
0
votes
1
answer
154
views
How to make a Query to write cell value based on content of another spreadsheet on GoogleSheets?
I've two spreadsheets and I want write the values of a column based on the values of another columns.
On the master spreadsheet I've two columns of interest: 'Ativo' and 'CNPJ'.
On the second ...
- 4,283
1
vote
1
answer
2k
views
Is there a way to bypass basic authentication in an Excel query?
I have an excel file, I get data from web, using API. My data returns in json format.
I schedule my query in 'query' and 'properties' so the data updates frequently.
I need to be able to share my file ...
- 87
0
votes
1
answer
609
views
Looking for a web scraper tool to extract entire table out of web pages and put them in different sheets in excel
The first column of this table contains all the links I have to work with: https://www.metabolomicsworkbench.org/data/DRCCStudySummary.php?Mode=StudySummary&SortBy=Analysis&AscDesc=asc&...
0
votes
0
answers
48
views
How do I pass parameters to my SQL statement?
How can I use parameters to create a dynamic SQL statement? I specified two parameters "DateStart" and "DateEnd" which I want to include in my Data source's SQL statement, but I don't know what the ...
- 13
1
vote
2
answers
17k
views
Is it possible to schedule the data refresh of a power query in excel to refresh data at 2 specific times every day?
I built a power query in Excel that pulls data from the newest file in a shared folder. It is an "OLE DB Query" with an SQL command type (not sure if that is relevant information but thought I would ...
- 11
4
votes
1
answer
7k
views
Power Query ignore missing column
I have made a custom function to transform files in a power query:
let
Source = (#"Sample File Parameter1" as binary) => let
Source = Excel.Workbook(#"Sample File Parameter1", null, true),...
- 415
0
votes
0
answers
43
views
Text to Column tool is interrupting my conditional formatting
I have a range of data that is pulled via a query, and then I use a data range comparison to highlight data. For instance, if I pull my data in sheet 'A', and input my own data in sheet 'B', I am ...
0
votes
2
answers
1k
views
How to "fetch first 1 row only" per IDs in 3 different columns?
I am attempting to fetch 1 row only based on unique set of IDs in another 3 columns. In my database, there are many records per date for each of a unique set of IDs in the other 3 columns (you can ...
- 69
0
votes
0
answers
27
views
Excel query for finding duplicates when the given table is not a pattern
I'm a beginner at Excel, I managed to solve almost all the problems encountered in the past month, but here I don't know how.
The required thing is to search in the table if there are more than a ...
- 1
3
votes
4
answers
32k
views
Tracking down the source of a dataformat error in a query
I have a query in Excel, retrieving data from a different workbook. Recently, however, the table the query writes data to has failed with the error DataFormat.Error: Invalid cell value '#N/A'..
Going ...
- 3,260
0
votes
1
answer
181
views
Format Issue on Excel Query Table
I am trying to pull the data with SQL query in excel. Query working fine and giving exact result but issue is I am passing the date variable 01-02-2005 in query and getting output -2006 (Last Column). ...
- 394
-2
votes
1
answer
5k
views
Connecting Excel and Smartsheet so excel can pull the files from a workspace of Smartsheet
I am trying to determine if upgrading Smartsheet to the business tier is worth it right now. The main thing deciding this is whether I can connect all of the files from a workspace to Excel Power ...
0
votes
1
answer
2k
views
Query Relative Path for Folder.Files
Excel 2016 VBA, Windows 10
I'm trying to use VBA to Get Data. I want to use a relative reference. I just want to get data from 'Raw Keyword.csv' in the same folder as the xlsm file. It never seems ...
user2585576
-1
votes
2
answers
3k
views
How to select more than 2 columns in =QUERY()
I was trying to copy data from Sheet1 to Sheet2 using query. But I want to select MAX(A) and all the other columns where data exists (B,C,D...etc.) and group by B.
То clarify, in Sheet1 I have some ...
0
votes
2
answers
98
views
Query Excel where Rows and Columns are reversed
How can I query an Excel file where the rows and columns are reversed / rotated 90 degrees?
Can it be done with a SELECT query, or do I need to recurse the cells programmatically?
It's for a .NET ...
- 2,313
0
votes
1
answer
984
views
Excel VBA listobjects.add querytable error 9
I aim to get data from a csv file using an SQL request. I already made it successfully in a previous Workbook, so I copied it and modified it only on the SQL request part. I have an execution error 9 ...
