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].
242
questions
9
votes
1
answer
27k
views
Loading a json file into Power Query
I am trying to load a JSON file I pulled from data.gov into Power Query for manipulation, but I can't figure out how to get Power Query to convert the JSON file to a tabular format.
Is there a way to ...
- 93
6
votes
1
answer
3k
views
Is it possible to use Power Query editor and Excel side by side
When Power Query editor is opened it does not let me edit, change anything in the opened excel workbooks.
I want to edit an Excel sheet, while working with Power Query Editor. Is it even Possible?Is ...
- 73
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
4
votes
4
answers
43k
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 ...
- 7,435
4
votes
2
answers
4k
views
Transform multiple columns in Excel table into multiple rows
I have an excel table in the following format.
ID Date1 Date2 Date3
1 1/1 1/2 1/3
2 1/2 1/3 1/1
3 1/3 1/2 1/4
Is there a way to change it into this format?
ID Date
1 1/1
1 1/2
1 ...
- 243
3
votes
1
answer
2k
views
Does Microsoft Power BI have file size limitations for functionality like Excel Power Query does?
I inherited an Excel data model consisting of multiple workbooks. Each workbook has multiple tabs utilizing Power Queries to get, transform and load data sets from base supporting Excel and csv files....
- 31
3
votes
2
answers
11k
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 ...
- 666
3
votes
1
answer
2k
views
Changing font size in Power Query
I have started to fiddle with Power Query a bit and thing that is pretty annoying is size of the font in formula bar and advance editor. Its way to small. Also when you manually enter formulas in ...
- 489
3
votes
2
answers
15k
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 ...
- 55
3
votes
1
answer
1k
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 ...
3
votes
1
answer
4k
views
Power Queries in Excel just default to "anonymous" and doesn't ask for login info
I have created multiple Excel sheets for my coworkers, many of which involve power query connections to our corporate SharePoint site. When I set it up, I logged into my SharePoint account to ...
- 182
3
votes
0
answers
1k
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, ...
- 471
2
votes
2
answers
2k
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 ...
- 123
2
votes
2
answers
154
views
How to delete the first row from the list in a column in power query?
I have a table with a column called custom:
I would like to delete the first row of every list in the column:
Is there anyway to do this?
The code is as below:
let
StDt = [start],
AllDates =
{...
- 157
2
votes
1
answer
1k
views
Power Query Editor: Why are null Values Matching on an Inner Join?
I am using Power Query Editor in Excel for Microsoft 365.
Table A has a column ID that holds a unique identifier. This identifier is usually numeric, but it sometimes contains text. Given this, I've ...
- 1,019
2
votes
2
answers
22k
views
List an AD Group Membership using Power Query / Data Explorer
I'm trying to pull back the members of an AD distribution group using Excel's Power Query tool.
Using the Active Directory data source I can query for all users on a domain. Following is the ...
- 840
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=*&...
- 147
2
votes
1
answer
1k
views
How to use the Table.SplitColumn while also keeping the original column?
I have a column with first and last name, which are always just two names with a space between them.
I'd like to have 2 new columns with First name and Last name but I still like to keep the original ...
- 946
2
votes
2
answers
2k
views
Excel Power Query Load Times - Why do dependent queries reload CSV file data?
I have a series of queries in Excel which all depend on an initial query which loads data from CSV files, processes them and loads them into a table in the worksheet. The remaining queries all use ...
- 31
2
votes
1
answer
2k
views
Outer join with union in Excel 2010 using Power Query
I've got two tables (two worksheets of the same workbook in Excel 2010):
The result should look like this:
My SQL code works well:
SELECT table_2.ID, table_1.value_1, table_2.value_2 FROM table_1 ...
- 131
2
votes
1
answer
468
views
Excel sheet with query to local file does not work on other machine
I have created an Excel sheet that pulls data from local files with powerquery.
The sheet does not work on my colleague's machine. We are not sure why.
It works like this:
Paste path to local file ...
- 31
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
2
votes
1
answer
889
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 ...
- 23
2
votes
0
answers
26
views
Excel Power Query
Want to create 3 queries that have a column filter in them. Rather than set the filter value by using a hard value I would like the filter value to be decided by a cell value in the workbook.
So ...
- 21
2
votes
1
answer
1k
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 ...
- 2,371
1
vote
3
answers
5k
views
Excel Power Query Editor icons and text too small or too large
I have a Dell XPS13 laptop whose recommended resolution is 3840x2160 and the recommended scaling is 300%
These are the values I use and generally everything works fine.
Sometimes when I try to use ...
- 6,840
1
vote
2
answers
13k
views
Connect to PostgreSQL database from Excel 2013 Power Query with Npgsql
I want to pull data from a PostgreSQL database on my local network with Power Query. By default my version of Excel lists three options under the "Get External Data/From Database" menu on the Power ...
- 461
1
vote
1
answer
442
views
Excel Power query how to replace a cells value with another if it is null
I have a column that could be null, if it is null I want to replace it with the value in another column as below
Prior to transform
Source Column
Replaceable Column
Source 1
null
Source 2
Rep 1
...
- 1,099
1
vote
2
answers
120
views
Power Query Editor: Strip Leading Zeroes from Numeric Values Stored as Text
I am using Power Query Editor in Excel for Microsoft 365.
Table A has a column ID that holds a unique identifier. This identifier is usually numeric, but it sometimes contains text. Given this, I've ...
- 1,019
1
vote
1
answer
394
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 ...
- 147
1
vote
1
answer
3k
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 ...
- 181
1
vote
1
answer
2k
views
Excel - power query (or anything else) - join on partial match between fields?
In power query (or MS query, or really anything else that would create a refresh-able table that gives me the desired results in excel), how can I join on partial matches between two fields?
I want ...
- 525
1
vote
1
answer
942
views
'From azure' button missing on Power Query ribbon
I have installed Power Query to access an azure SQL database. However I don't see a 'From azure' button which seems to appear in the screen shots in the tutorials. How can I activate this? Is there ...
- 23
1
vote
1
answer
400
views
Power Query: Splitting Multi-Level Cells
I am using Power Query in Excel for Microsoft 365.
I have the following source data:
I am trying to determine the needed transformations to obtain the following desired result:
The source data has a ...
- 1,019
1
vote
1
answer
586
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 ...
1
vote
1
answer
4k
views
How to allow multiple users to refresh to sharepoint Excel file with Power Query
I have an excel file with data and the second excel file that uses Power Query to connect and fetch data from that 'data' file. They both are located in the Sharepoint space.
When I set up a ...
- 11
1
vote
2
answers
539
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 ...
- 147
1
vote
1
answer
1k
views
Need a column within a table to maintain data manually entered after data refresh
I have a table in excel which is populated using a ODBC connection to a SQL database.
This table contains orders and the status of each order (the status is changed by a third party program), I have ...
- 11
1
vote
1
answer
6k
views
Oracle.DataAccess.Client and .NET Framework errors in Microsoft Excel
When trying to access an Oracle 12c Database through Excel and Power Query, I received an error stating
The recommended provider("Oracle.DataAccess.Client") is not installed. You
can continue ...
- 33
1
vote
2
answers
3k
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 ...
- 13
1
vote
1
answer
33
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).
...
- 33
1
vote
1
answer
2k
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
------------|----------...
- 5,777
1
vote
2
answers
2k
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
2
answers
6k
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
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
1
vote
3
answers
6k
views
Parameterizing IN clause in PowerQuery SQL query
I've seen examples (and coded one) that parameterize a PowerQuery SQL statement with values read from cells in the spreadsheet'a table. But these always deal with simple logical conditionals like ...
- 175
1
vote
1
answer
248
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
1
vote
1
answer
22
views
reformatting people-planning table in powerquery
I have a table:
Date
personA
personB
personC
1/1/24
x
H
2/1/24
x
Y
3/1/24
A
x
4/1/24
Y
x
5/1/24
x
x
Above the table I have a table with a title "Person" and 1 cell with a dropdown ...
- 13
1
vote
1
answer
451
views
Power Query: Perform Applied Steps for a Column Only if it Exists, Avoid Error if it Doesn't Exist
I am using Power Query in Excel for Microsoft 365.
Please consider the following M Language code:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type ...
- 1,019