Questions tagged [power-query]
For questions related to the Microsoft Power Query add-in for Excel. Use this tag together with [microsoft-excel] and, if relevant to your issue, the tag for whatever kind of data source you are querying, for example [microsoft-access] or [mysql].
206
questions
0
votes
0
answers
6
views
Sharing an excel file with multiple data connections with other excel file
I have built an Excel dashboard.
The data that was used to build it is in my system and as the data files were very large in size, I have used data connections to take pivots and make charts.
but when ...
0
votes
0
answers
28
views
How to change change Excel PivotCache from external source to table if external source includes measures
I've inherited a worksheet that contains an external connection via PowerQuery, which is connected in turn to 20 or so PivotTables and four or five common slicers. Users have complained about speed ...
- 1
0
votes
0
answers
17
views
Can you create a calendar in Teams with data from an Excel Power Query?
I currently have a Power Query that compiles data on deliverables (projects, document numbers, titles, due dates, etc.) from multiple worksheets. Is it possible to use the data from the Power Query to ...
- 96
1
vote
1
answer
31
views
Where is the SQL connection string and sql query text stored in Excel file
I have an excel with some SQL data loaded using power query.
I wonder whether the connection string and SQL query are included in Excel file. It would make it unsafe to share.
- 61
0
votes
1
answer
30
views
Import key-value text file using PowerQuery
I need to import some large log files from a firewall into Excel.
A truncated version (there are nearly 100 pairs per record) looks like this:
"itime=1682240966","date=""2023-...
- 162
0
votes
1
answer
49
views
Assign group ID to rows (tags)
I have an Excel 365 table of log entries. The logs are grouped by a start tag and end tag.
Column C: =IF( LEFT([@LOGS],6)="<Event","start", IF(LEFT([@LOGS],7)="</...
- 137
1
vote
1
answer
36
views
Use Column1 as column headers, use Column2 as rows
I have a table in Excel that I've used in a PowerQuery query:
I want to pivot the table so that:
Column1 is used as column headers
Column2 is used as rows
Like this:
2 ...
- 137
0
votes
2
answers
54
views
Concatenating adjacent rows with Excel power query
Suppose I have a power query in Excel that produces data like this:
Col1 Col2 Col3
Item11 Item12 Item13
Item21 Item22 Item23
Item31 Item32 Item33
Item41 Item42 Item43
...
0
votes
1
answer
60
views
Transform list data into formatted matrix based on values using Power Query
Background
I recently took a position at an orchard. The planting maps and other data are stored in many different files, each with different formatting and structures, maintained and created by ...
- 3
0
votes
1
answer
68
views
Power query: split specific columns into seperate row
Not sure if the title really describes my problem and if Power Query is the right way to get this. My table contains events with individual IDs and invoices that belong to a certain ID:
TYPE
ID
...
0
votes
1
answer
445
views
How to import JSON data into EXCEL columns?
I have an excel spreadsheet like following:
with a first column the name of something, and then two columns "date" and "owner". I also have the following JSON file which I want to ...
- 512
0
votes
0
answers
81
views
how to automate adding new data every month
Every month, I add new data from csv file to an excel table. How can that be automated? Looking at the table photo I attached, I have two questions:
I want to automatically add new columns with data ...
0
votes
1
answer
21
views
R studio, Excel, XLS files
I have 20 excel (.XLS) files. Each excel contains 13 Sheets. All of these excel are in proper format. I wanted to extract sheet no.10 from each excel and paste the data into an Excel sheet. I want ...
- 3
0
votes
1
answer
30
views
If rows are missing for certain years within sysdate-based year range, generate filler rows using Power Query
I have a table in Excel 2016:
YEAR AMOUNT
---------- ----------
2024 100
2025 200
2025 300
2026 400
2027 500
2028 ...
- 137
0
votes
2
answers
353
views
Combine rows in Power Query based on a shared column value
I have an export from quickbooks showing all outstanding invoices. Each invoice consists of only 2 line items: "Consulting Fee" and "Admin Fee". The QB export creates a row for ...
- 1
0
votes
2
answers
191
views
Transpose Group of Rows to Single Row | MS EXCEL
I need to Transform Some Uneven Group of multiple rows to single row
I Tried to Extract the Required data from multiple row to multiple Column. But all data placed under different Rows (same as input ...
- 115
-1
votes
1
answer
67
views
Power Query Load Selected Data from a List
I work in Logistics, and we deal with Tracking IDS.
I have heavy data of over 100k rows Containing all data related to Tracking IDs (Dump)
I have a list of tracking IDs mostly in hundreds which I have ...
0
votes
2
answers
361
views
Move data from multiple columns into one and remove duplicates
I currently have a situation wherein I require the unique data from multiple columns to be moved/identified in one single column.
The data currently looks like this:
Apple
Microsoft
Dell
U.S
U.S
...
0
votes
1
answer
77
views
Loading from Power Query to Data Model with Excel 32 bit
Is it possible for you who use Excel 32 bit (check File-->Account-->About Excel) to load from Power Query to the Data Model and update the query afterwards?
My private office is 64 bit and ...
- 223
1
vote
0
answers
100
views
Configuration of Excel's Power Query "Trusted" data sources for windows authentication
I made simple WebApi, which returns data in JSON format for http GET request. The request is made from Excel (2013) through Power Query.
In order to receive a response from WebApi endpoint, a user has ...
- 11
0
votes
0
answers
159
views
Power Query Opens Source Data and Then Can't Access It
Full disclosure, I'm kinda new to Power Query but I'm running into some weird problems. I'm used to using Data Connections in older versions of Microsoft Office to connect an Access database with an ...
- 1
0
votes
1
answer
90
views
Use Power Query result as URL for follow-up query?
I'm not sure if this is possible, but I am working with an API that gives a URI as a value that I'd like to be the source for other queries (for each result). Imagine an initial Web query returns a ...
- 153
0
votes
2
answers
706
views
Excel 2016 Power query editor missing
I have a user whose Excel 2016 Power query editor is missing under Data Tab-> New Query -> Query Option. In the Query Option window, Under Global - here Power Query Editor should be listed but ...
- 3
0
votes
1
answer
15
views
Creating a workbook based database-calculations-display system in Excel
I'm trying to make an Excel based modular reporting system so that each sections can be modified without messing up everyone else's reports.
INPUT / Facts table: I have a database that is currently ...
0
votes
1
answer
108
views
Manual input column with restricted allowed values in power query imported table
I'm using the Power Query Editor in Excel in order to obtain a table composed of the content of all the CSV files that I put in a specific folder. This table contains a list of bank transaction. When ...
- 103
1
vote
1
answer
393
views
Do data source credentials in Excel Power Query expire?
Sometimes users are asked for credentials when trying to refresh queries, even though they have entered credentials before. I'm wondering if the credentials can expire. Data sources include SQL ...
- 13
0
votes
1
answer
106
views
look up missing values with PowerQuery
In the green table we fill in certain columns manually. Some columns (Fat, Protein) we want to get filled in from the blue table with PowerQuery.
All entries have a unique ID, which match between the ...
- 223
0
votes
2
answers
1k
views
Best way to extract a table from a website (ESPN) into Excel
I have a page in my ESPN.com league that has a lot of data I'd like to extract to Excel. I've included a screenshot below.
I've tried simply copy and pasting but the player images get in the way. I've ...
- 123
0
votes
1
answer
120
views
Excel FTP Power Query getting no data on one computer
I have long used the following simple Excel FTP query to populate a Table. As shown in Power Query Editor:
let
Source = Csv.Document(Web.Contents("ftp://shortstock@ftp3.interactivebrokers....
- 1,441
0
votes
0
answers
47
views
Getting Pivot Table to show average OR weighted average values in same column field
all! I've a bit of a complex issue I'm trying to solve, and am hoping it's possible. Apologies for the long post. I'll be happy to provide more details upon request.
tldr - Giant pivot table has a lot ...
- 1
0
votes
1
answer
265
views
Return employee time zone from AzureAD into Excel as non-admin user
How do I retrieve a user's home base time zone as a UTC offset through a query, accounting for current DST settings?
I'm a regular non-admin user. I use Power Query* to retrieve our employee list from ...
- 171
0
votes
1
answer
458
views
How do I transpose an Excel table to unique columns from many duplicate rows? (preferably in PowerQuery)
I've looked everywhere, and tried a lot, seemingly most people don't need this problem solved :-(
I have a table with two columns.
Column A (TAG) has 4 distinct values, repeating in that pattern over ...
1
vote
1
answer
222
views
Excel Powerquery taking about 4 hours to refresh
I have written a series of PowerQueries that calculates an employees rolling 12 month sickness taking into account their shift pattern (may not be Mon-Fri) and their contracted hours.
It returns the ...
2
votes
2
answers
796
views
Excel How to transform data from wide to long based on multiple columns
I am a student learning more advanced Excel and I was curious if Excel had some built in features to handle pivoting more complex data. Or would it need to be scripted from scratch?
Say I have table ...
0
votes
1
answer
834
views
Delete Specific Rows from Excel from a List of Row Numbers
I have a worksheet with 83,188 rows and 4 columns of data.
I have another worksheet that has a list of row numbers (eg 18751, 9127, 696, and etc) that signify the rows I would like to delete from the ...
0
votes
2
answers
460
views
Excel 365: Is there a way to fully protect (hide or encrypt) the url provided for a Data Source?
I'm importing data to Excel 356 using a data source "From webb". The url provided includes an API key that I do not want to share with others when I share the Excel file. I can use Protect ...
- 11
0
votes
1
answer
193
views
Convert from table to matrix format with Excel 2010
I would like to convert this data from table to matrix format where the names in the dest column become columns and the number values corresponds to the total but the origin column stands still
origin ...
0
votes
2
answers
329
views
How to add Epi Week aka MMWR Week column in Power BI
My dataset has a date column. I need to assign the correct Epi Week/MMWR Week to each row. I can't do it by hand because the dataset spans five years and has over 18,000 rows.
The Power Query function ...
- 230
0
votes
1
answer
988
views
List.Sum in Power Query is not ignoring null values
I added a column with this code:
= Table.AddColumn(#"Changed Type", "Total Quantity", each List.Sum(
{[species_males]+[#"species_females-mixed"]
}))
List.Sum is ...
- 230
0
votes
1
answer
300
views
Extracting all matching (or close to) values between given START n-th and END n-th values / using Power Query
Consider having two tables. Table1(T1) has Date/Time and Pressure value columns and Table2(T2) has also Date/Time and Info values columns.
T1 is showing pressure change through time, for instance: 21....
- 121
0
votes
1
answer
1k
views
How to use Get Data in Excel without loading the data fully in Excel
I have a minor nuisance regarding loading the data into Excel. I load data from a SQL database as follows: first go to the tab Data > Get Data > From Database > From SQL Server Database. ...
- 171
0
votes
2
answers
2k
views
"Speak Cells on Enter," not showing in Excel Options -> Quick Access Toolbar -> All Commands
Where is, "Speak Cells on Enter?
Hopefully the attached screenshot says it all. "Speak Cells on Enter," is not showing up in Excel Options -> QAT -> All Commands. Anything I need ...
0
votes
1
answer
42
views
Excel 365 - Dynamically importing data into excel from a Database
I have a spreadsheet with 40k rows or so which is added onto every week. Each row has an Employee ID, Employee’s Shift Number and Hours worked column (see attached image for sample data). Sample data
...
- 11
0
votes
2
answers
182
views
Creating multiple values from single value + monthly increment
looking for any advice/ tips that could help with the creation of this dummy data set
From this table, want to create another table
ID MOB date(dd-mm-yyyy)
A01 2 01-07-2019
A02 3 01-02-2019
This ...
- 1
0
votes
1
answer
997
views
JSON conversion in Power Query - how automatically get list of object property names
This is a question based on comment in Excel: Power Query - how to repeat block of code for each row in a table.
Let's say we have a JSON with array of object each with N properies:
{
"data&...
- 825
0
votes
2
answers
1k
views
Excel: Power Query - how to repeat block of code for each row in a table
I have JSON in format like this:
{
"data": [
{
"date": "2021-01-01",
"data": 10
},
{
"date": "2021-01-02",
...
- 825
1
vote
1
answer
773
views
How to add a measure to Excel PowerQuery which computes the cumulative value?
I have the following question regarding Power Query. For the correct setup, please enable Power Pivot (in COM Add-ins in Excel):
Let us first consider the following example:
I have inserted the text ...
- 171
0
votes
1
answer
6k
views
Power Query issues after initial run of the query
I have a fairly simply power query built that collects data from 12 sheets in a single workbook and consolidates them into a master list.
The data/columns are consistent across this workbook
The ...
- 192
0
votes
1
answer
55
views
powerquery filtering data in import time
I have a data file I want to import into excel
alpha,customer,10
alpha,town,12
bravo,customer,6
bravo,cars,5
charly,cats,5
(actual file is about 15 columns and 1000's lines)
I can import into excel ...
- 1,677
0
votes
1
answer
207
views
Transforming ugly survey data in Excel / Power Query
I have input data from some surveys that requires some transformations. I've been playing with Power Query Transpose function, but I am clearly missing some important knowledge to do the required ...
- 3