Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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].

0
votes
0answers
6 views

Load many csv files at once in Power BI

I received a relational database export where each csv file represents a table. Just 107 files, and i need to import them all to Power BI... Am i out of luck and need to import each of them ...
0
votes
0answers
18 views

OData Feed / Power Query in Excel 2016 Not Retrieving All Records

In Excel, I successfully connected to an OData feed from Data.Medicare.gov (the website is https://data.medicare.gov/Hospital-Compare/Healthcare-Associated-Infections-Hospital/77hc-ibv8/data and the ...
0
votes
0answers
18 views

Copy But Unable to Paste within a Excel Worksheet

I have two worksheets in an excel file in which I can't paste copied data within the worksheet. I click on the next cell after copy and the rotating dashes disappear or when I right-click the paste ...
0
votes
1answer
15 views

Power query - turning number strings into numbers while preserving text

I have a set of data: 1 2 3 Infinity 4 5 The numbers are registered as strings inside of power query for some reason. I'd like to convert the numbers to the percentage type (Or any other type ...
0
votes
1answer
20 views

Excel: how to import identically formatted CSV files

Apologies if this has been asked and answered before - I have looked but not found. I get CSV files every two weeks that are identically structured, and I need to import them to the same Excel 2016 ...
0
votes
0answers
29 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
1answer
22 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. ...
0
votes
1answer
33 views

Excel group transposed columns by date

I'm having trouble transposing data to get the format that I'd like. I have pay period end dates in one column and employee names in the other column, and I'd like format it so that each column header ...
0
votes
0answers
49 views

Display a countdown timer for web query refresh

I have a web query set up to refresh data when the workbook is opened and then refresh data every 15 minutes, but I would like to display the last refresh time and the remaining time left until the ...
1
vote
0answers
26 views

Excel spreadsheet/workbook data connections: Two way communication with applied filter?

So let me pose you guys a scenario. There are several users all inputting data into a single spreadsheet. This work is initially assigned to these users by a manager who, after the data is input, ...
0
votes
1answer
55 views

Excel Transpose Certain JSON Fields

I'm pulling data into Excel via a webhook (JSON). It appears in Power Query Editor like this: | name | groupName | key | value | | host1 | group1 | tag1 | val1 | | host1 | group1 | ...
1
vote
0answers
44 views

Excel PowerQuery cannot find SharePoint files after a certain date

Background I have a number of different Excel files which connect to a number of different SharePoint folders for a variety of purposes. Each week, new data is exported from our client systems and ...
0
votes
0answers
142 views

Retain cell formatting after data refresh in Power Query Excel

I want to retain the column width and cell formatting whenever I refresh data in Power Query. Data formatting under Table Tools > Design > External Table Data > Properties> unchecking "Adjust column ...
0
votes
1answer
15 views

Grouping Date/Time by hour in pivot table not working as expected

I have one Excel table appended to another (over 1.1 million rows so can't fit all of the data on one sheet) via a query in excel. I am using this append query as the source for a pivot table. When ...
0
votes
0answers
17 views

Automatically update a Power Query after data was changed in source(another sheet in same workbook)

I have two sheets in a workbook. 1st sheet is data source for my Power Query. I created this Power Query by selecting my predefined table then Data >> From Table/Range and then I load this query into ...
0
votes
1answer
24 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 ...
0
votes
0answers
41 views

How to use group table data by start and end dates

After unpivoting my table using Power Query, I have this table below. My next objective is to calculate the task allocation for each person by month in a single pivot table. However, the start and ...
0
votes
1answer
43 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 ...
0
votes
1answer
120 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. ...
0
votes
1answer
72 views

Power Query - Value.Divide(Value.Add( )) returns all “null” results

When I create a calculated field in Microsoft Power Query feature ("Get and Transform" since Excel 2016) I use a combination two simple formulae: Value.Divide() and Value.Add(). For some reason, this ...
0
votes
1answer
112 views

How to use Power Query to create pivot tables with non-numerical values

The data is transformed into an Excel table and loaded into Power Query. It looks like this: (I don't understand why the table doesn't work. After a few searches I realize that the site does not ...
0
votes
1answer
113 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 ...
0
votes
0answers
32 views

Combine 2 tables on multiple key values - EXCEL

So, I am having an issue relating to combining 2 tables with 2 corresponding (relative) fields. these fields being: Destination & Date - the primary goal is to combine revenue from the second ...
0
votes
0answers
30 views

Clearing all data and authentication information from Excel Power Query Connections

I am developing a spreadhsheet that will query the Azure AD Graph API that I want to make freely available. Naturally, when I develop it I will use my Azure AD users, and query my tenants data. ...
0
votes
1answer
28 views

Custom Column with Origin of Table

I have a power query with 14 tables from 14 sheets. The columns on each table vary. Some have columns unique to a sheet. They all share about 10 columns, but the order varies. How can I go about ...
0
votes
1answer
216 views

Excel how to join separate tables

I am working with a collection of tables (in different workbooks) that share the same structure. These are the job registers for a given year, and the primary key for each item is the year (2018) plus ...
3
votes
0answers
510 views

How do I get a cross-join with power query to “remember” values associated with it?

Let's say you have a basic Excel cross-query. You have two lists: A B C and X Y You cross join them (Cartesian join them), and get the following table: A - X A - Y B - X B - Y C - X C - Y Now, ...
1
vote
0answers
21 views

Updating a Power Query automatically when adding rows

Whenever I add some rows to my query, the left side of my workbook doesn't update. I have to manually update it each time. Is there a way to get it to update automatically?
1
vote
1answer
221 views

Trying to export single column's data from large CSV file

Problem: I am trying to export a single column of a .CSV file that is 7 gigs in size (large enough that I can't open the entire file in excel... or most text editors for that matter) to a smaller file ...
0
votes
0answers
22 views

“pivoting” two tables together to generate a new table

So I have three table columns that I'm trying to pivot together, in separate locations. All three columns have been added to my data model. Here's what I have, here's what I'm trying to do: Table ...
1
vote
1answer
319 views

Duplicating Steps in PowerQuery

Is there anyway in Excel Power Query to duplicate the Applied Steps from a prior query so that all of the applied steps show in the duplicate? As in, I'd like to duplicate a 10 step query to step 6 ...
0
votes
0answers
76 views

Excel: Connecting to an online HTML table missing the <table> tag

I'm working in Excel 2016. My organization switched the location where it publishes a table for a report, and I have been tasked with redirecting the connection on a spreadsheet. I'm not an Excel ...
2
votes
1answer
257 views

How to call Power Query M function from external Workbook?

If I have an Excel workbook that is available online and this workbook contains a couple of spreadsheets and some Power Query M functions, is it possible to invoke these functions from another ...
0
votes
1answer
3k 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 ...
0
votes
2answers
2k views

Use a Cell Value for a Paramater Value using in Power Query

I am using a query like below in power query Excel 2016: let Source = Odbc.Query("dsn=AS400", "select * from libm61.emleqpm1 where STN1 = '03' ") in Source I want to replace '03' with a ...
0
votes
3answers
57 views

Pick top 3 values and sum them

Let's say I have some data that looks like this: PLAYER |Team |Points Smith |Suns |25 Jones |Suns |15 Martin |Suns |23 Chen |Suns |3 Williams |Suns |...
1
vote
1answer
20 views

Doing calculation in PowerPivot (Excel 2010) based on two tables

I am on Excel 2010 with PowerQuery and PowerPivot add-ins installed. I have two tables, where: Table1 has amounts denominated in different currencies. Table2 has conversion rates (exchange rates). ...
2
votes
3answers
3k 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 ...
0
votes
1answer
270 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
1answer
647 views

Using Excel 2016 Power Query to Perform Conditional Inner Join

I have two tables in Excel 2016, a Vendor table, and a Transactions table. I'm trying to do something like this from SQL but in Power Query's Formal Language (M): SELECT * FROM Transcations INNER ...
0
votes
1answer
547 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 ...
2
votes
1answer
738 views

Excel - Using SQL command on Power Query data

Using Power Query, I pull in two reports from Salesforce using Power Query > Get external Data > From Online Services. I load them into tables in two different sheets, and then create a third ...
1
vote
1answer
137 views

Use Excel to format denormalized table as report with summary headers and detail rows

It is common where I work to obtain a CSV or Excel sheet that contains denormalized report data. Here is a simple example: Category ID | Category Name | Detail one | Detail two ------------|----------...
0
votes
1answer
621 views

How to merge multiple tables (5) with a common column

How can I merge multiple tables, similar to Excel - How to merge rows from 2 tables based on a common column? But I have about 5 different data sets with a semi common column - i.e. say for 800 rows ...
0
votes
1answer
1k views

Excel Power Query Error - Expression.Error: The argument value isn't valid

I'm trying to copy sheets from one workbook to another by copy and pasting the queries. These queries work fine in the original sheet but when I copy them, I get an error Expression.Error: The ...
1
vote
2answers
1k views

Power Query: Delete three character substring based on 2 character substring match

I need to filter a bit of constantly changing, but similar garbage text from a query source. The junk text always takes the following form: X?* <- where * is a wildcard and "X?" is the literal ...
1
vote
2answers
5k 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 ...
0
votes
1answer
617 views

Loading a json key-value map into Power Query

I'm trying to load my Firebase data to Power BI, the data is a JSON map of the following format { "soh4gf1k": { "name": "bill", "age": "40", "maritalStatus": "married" ...
0
votes
1answer
3k 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 ...
0
votes
0answers
39 views

File Path button missing in source step after upgrading Power Query

After upgrading MS Power Query to the Dec 6 version I noticed that in my existing query for cleaning up a CSV file that the "file path" button is missing in the source step. Prior to the upgrade I ...