All Questions
Tagged with microsoft-excel-2016 power-query
23
questions
0
votes
1
answer
89
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 ...
- 157
0
votes
2
answers
546
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
2
votes
2
answers
1k
views
Select JSON records as Excel table via Power Query
I have a URL that points to a sample server that returns JSON records:
https://sampleserver6.arcgisonline.com/arcgis/rest/services/LocalGovernment/Events/MapServer/0/query?where=1%3D1&outFields=*&...
- 157
0
votes
1
answer
346
views
Designing a mass attendance system with Excel and PowerQuery
I am building an Excel spreadsheet for tracking a large number of personnel's daily attendance records. All data examples shown here are fictional, including the data structure. Only the gist of the ...
- 103
1
vote
2
answers
585
views
How to document Excel Power Query steps?
I have an Excel 2016 spreadsheet that has a Power Query sheet in it. The query is used for transforming data.
The query has multiple steps.
While some of the steps are straight forward, others are ...
- 157
0
votes
1
answer
37
views
PowerQuery in Excel 'master' spreadsheet on SharePoint can only be refreshed by one user and gives authentication errors for others
I created a group of SharePoint libraries on an intranet that each contain an identically formatted spreadsheet, and another library that contains a “Master” spreadsheet that fills a table from the ...
0
votes
1
answer
177
views
Background Refresh Not Refreshing
I am pretty new to excel/power query, so I apologize if this is a basic question.
I'm using an Excel 16 document on sharepoint that is pulling data from another sharepoint document. The data queries ...
- 101
0
votes
0
answers
51
views
Power Query; Calculating when a certain value in numerical order is missing and creating an additional row
Hello Fellow Super Users,
I generate a report every week that identifies demands for parts that my company makes for the current week and future weeks. The report I generate loads data from a ...
-1
votes
1
answer
182
views
Excel: New Powerquery from Web (csv) doesnt work
I want to build a worksheet where the user can input a Stock symbol and the balance sheet is automatically downloaded from morningstar to a table, and then some custom formulas are run on the data.
...
- 350
0
votes
1
answer
860
views
Power Query with Additional Column
I am using a power query to read rows indexes by an item number from one file I receive to one file I am maintaining. I need to add some columns that are editable by me (cell editing), in a way that ...
- 43
0
votes
1
answer
553
views
PowerQuery ODBC connection revokes password: due to auto-refresh?
I have a nice PowerQuery query that connects with ODBC to a database. Our passwords change every month. When I enter GetAndTransform to edit the query, that time of the month - even if I don't ...
- 189
0
votes
1
answer
594
views
Excel 2016: Query from folder not combining csv's by header row
I'm trying to combine multiple csv's in Excel 2016 using the New Query->From File->From Folder. I have done this in the past without any issues so I'm not sure what I'm doing wrong this time.
...
- 1
0
votes
1
answer
2k
views
Excel - Is it possible to make a drag and drop function to save files directly into a folder?
I am working on a EDMS system which is to be created in Excel. Could anyone clarify whether or not it is possible to drag and drop files directly to the folder path THROUGH Excel? Instead of accessing ...
5
votes
2
answers
26k
views
Excel Power Query date format messup
I'm trying to import dates from a csv file to Excel. The input is in the format of m/d/yyyy, and the same is my system setting:
However, when I change the Data Type setting upon importing, it messes ...
- 197
2
votes
3
answers
12k
views
How to set a cell value using the result of a parameterized SQL query using Power Query?
I have a worksheet with columns A, B and C. Then I need to run a query on my SQL Server using the values from columns A and B to find the value that should be placed on column C.
I've created the ...
- 145
0
votes
1
answer
521
views
Cannot connect to a shared OneDrive (Personal) file through Power Query
I first upload an Excel file to Onedrive, then go Onedrive and share the file, getting a link with editing rights.
I then go to Power query Data - New query - From other sources - From Web and ...
0
votes
1
answer
2k
views
Power Query - how to connect to sharepoint list using VBA
anybody knows how to connect to SharePoint Folder using VBA for Power Query? I need to perform ETL(Extract,Transform & Load) process using Power Query, and would like to use VBA to automate the ...
- 115
1
vote
2
answers
7k
views
Use parameters in Power Query
Please help me figure out how to parametrise a Power Query.
I have built a PowerQuery query which queries a webservice for certain data and manipulates it in the right way. Let's say I query a task ...
- 121
0
votes
1
answer
5k
views
How to change Data Source in Power Query for Pivot Data? (Browse Button missing)
Ok, short explanation what I did: I created a Report in a Workbook ("Main File") linked with Power Query to another Workbook ("Source") which contains my data source. I have multiple Queries, 8 of ...
- 1
1
vote
2
answers
1k
views
Excel Power Query EndOfWeek
I have a Power Query column of dates. I need to transform the rows of this column to the end of their week. How do I do this with the end of the week being based on Sunday?
The default "...
- 11
1
vote
2
answers
3k
views
Excel 2016 list of options for connection to DB has suddenly reduced, (MySQL, PostgreSQL)
I work with open source dbs such as MySQL or PostgreSQL as a back-end data storage with my excel 2016.
I had the full list of connections to all different kinds of dbs but they are suddenly reduced ...
- 123
0
votes
2
answers
545
views
Add custom category type column to an Excel 2016 Power Query
Used the power query feature of Excel 2016 to import several CSV files containing personal financial history and trying to use the power pivot thing.
Problem is I really need to group the ...
- 39.9k
1
vote
1
answer
251
views
Loading data to power pivot via power query vs. directly to power pivot
If I'm fine with doing everything in PowerPivot, aside of future proofing in case I may want to edit queries in the future, are there any reasons to put Power Query buffer in between my data source (...
- 11