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].
Questions tagged [power-query]
254 questions
9
votes
1 answer
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 convert JSON to a table in Power Query without…
CountZero
- 93
8
votes
4 answers
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 value form cell AD2
Is this possible to do ?
PeterH
- 7,595
6
votes
1 answer
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 import via "Get Data" -> "From File" -> "From JSON" to populate the…
Alex
- 387
6
votes
1 answer
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 so how?
Novice
- 73
5
votes
2 answers
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 the dates up. Apparently it switches to d/m/yyyy…
lte__
- 227
4
votes
2 answers
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 and then add in new steps from where it forks from…
DrewDaddio
- 65
4
votes
2 answers
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 1/3
2 1/2
2 1/3
2 1/1
3 1/3
3 1/2
3 1/4
I'm…
RWL01
- 253
3
votes
1 answer
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 authenticate.
The reports refresh for me easily. However,…
Greg Viers
- 192
3
votes
1 answer
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 custom column its tiny as well. Is there any way that…
IGRACH
- 478
3
votes
1 answer
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.
Over time the files of the model have grown…
Sonseeahray
- 31
3
votes
2 answers
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 worksheet tables as a source and don't utilize…
user175324
- 41
3
votes
0 answers
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, let's assign values to each of these numbers:
A - X -…
Selkie
- 471
3
votes
1 answer
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 Workbook?
I know how to load the workbook with…
KarmaEDV
- 2,371
3
votes
1 answer
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 worksheet and table using Data > Get external Data > From…
2
votes
2 answers
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 generated formula: =…
JohnLBevan
- 930