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].
239
questions
0
votes
0
answers
16
views
Importing CSV into Excel via Data -> From Text/CSV: Can I automatically apply the Power Query column formats?
Microsoft® Excel® for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20124) 64-bit
I have an external program that creates CSVs. Some of the columns have eg. $ or %. The import query is smart ...
0
votes
0
answers
44
views
How to have the same values for duplicated items in Power Query?
I've come across a scenario where I have duplicate values in the column ID without letters I'm trying to ensure that these duplicated IDs also have the same values in the Stock Unit, Type and Has ...
0
votes
1
answer
35
views
Custom columns in Power Query vs additional columns in Excel source
I have a report in Power BI that uses data from an Excel file. Is it better to have a very slim Excel table with values only and add all (possible) calculations via custom columns in Power Query or ...
0
votes
1
answer
89
views
How to open nested tables from power query, in new sheets?
In excel, I have a power query which loads a table with nested tables. In the query editor window, I can click the Table and drill down which is great.
Problem is, when I do save and close, and return ...
0
votes
0
answers
64
views
Power Query SQL bring in cell contents for the where statement
I am trying to build a flexible sql where statement so that my power query can pull the exact data the user specifies. I have an excel worksheet with about 18 cells that the user populates. I use ...
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 ...
0
votes
1
answer
42
views
How to expand the dataset with 0.5 day in power query?
I have a excel dataset with the following format:
I would like to generate Dates between Start and End Date. However, there can have non integer days in the duration column. How to expand the row if ...
2
votes
2
answers
123
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 =
{...
0
votes
3
answers
93
views
Using Excel to separate complex many-to-many relationships
I have a set of data in excel that follows the below format:
Table displaying data and its attributes/relationships
Image displaying relationships between High, Medium and Low level attributes
I need ...
0
votes
0
answers
55
views
How do I fix Excel instability after using PowerQuery?
I've been working on some VBA code to import and parse PDF files using PowerQuery. The code itself is working fine, but after some column-related errors (I had more columns defined than some of the ...
0
votes
0
answers
66
views
Power Query: Working Query Fails if Moved Out of Workbook into External File
I am using Power Query in Excel for Microsoft 365.
This post is a follow-on to this one:
Power Query: Combine Subset of List of Tables that Exist
The Power Query shown below is not the solution I ...
0
votes
3
answers
158
views
Power Query: Combine Subset of List of Tables that Exist
I am using Power Query in Excel for Microsoft 365.
I have a Power Query whose first step is to combine a set of tables:
let
Source = Table.Combine({One_foo_bar, Two_foo_bar, Three_foo_bar, ...
0
votes
2
answers
238
views
How to include a variable within M language PowerQuery
In Excel365 I am trying to build a query that includes some POST data, and I have it all working until I try to make those values variables that come from named cells in my Workbook. In the example ...
0
votes
0
answers
20
views
How to use a column in a queried table to update a column in the source table in Excel?
I have a source table that is being broken out into two different tables using Query. The tables are essentially just filtered versions of the source table. I would like for people to be able to use a ...
1
vote
1
answer
387
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 ...
0
votes
1
answer
176
views
Power Query: Splitting Multi-Valued Cells into Rows without Creating all Possible Combinations
I am using Power Query in Excel for Microsoft 365.
Some of my columns have multi-valued cells. I need to split such cells into rows. Consider this source data:
The M Language code for my Power Query ...
0
votes
2
answers
275
views
Power Query: Refer to a Column by Name Stored as a Text Parameter (REDUX)
I am using Power Query in Excel for Microsoft 365.
The question I am about to pose is very similar to one I recently posed and for which an elegant answer was given. That question is here:
Power Query:...
0
votes
1
answer
408
views
Power Query: Refer to a Column by Name Stored as a Text Parameter
I am using Power Query in Excel for Microsoft 365.
Here is my source data table (whose table name is Source):
Col1
Col2
Col3
Col4
Name
Alt.Name
11
21
31
41
Dog
Fido
12
22
32
42
Cat
Fluffy
23
33
43
...
0
votes
0
answers
57
views
Power Query: Parameterize the Tables a Merge Query Operates on
I am using Power Query in Excel for Microsoft 365.
I am trying to create a merge query to perform an inner join. The kicker is that I need to parameterize the tables to be joined.
I have a text ...
0
votes
1
answer
107
views
Power Query: Re-bind a Query to a Worksheet / Table that has been Deleted and Re-Created
I am using Power Query in Excel for Microsoft 365. I am using VBA, rather than the graphical interface, to perform Power Query operations.
Assume I have an existing workbook, with that workbook ...
1
vote
1
answer
407
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
vote
1
answer
395
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 ...
0
votes
0
answers
75
views
Excel query not removing row once deleted from source table with refresh
I have a source workbook that is an internal document, we want to share a "live" copy of a single sheet. I have created a new workbook and ran a query pulling data from that sheet. When I ...
0
votes
2
answers
652
views
How to use Excel to produce Timesheet from MS Outlook calendar?
This question is mainly to share my idea.
On daily bases I have problem to fill in my timesheet. I work different hours every day and at the end of the month I have to submit Excel timesheet that ...
0
votes
1
answer
334
views
Power Query: Why do Text Filters Remove null Values?
Problem Statement
I am using Power Query in Excel for Microsoft 365.
I have found cases where applying a text filter to a column matches null values. This results in undesired removal of rows. I have ...
0
votes
2
answers
129
views
How can I manipulate hundreds of thousands of rows of data without it freezing in excel?
When using an add in function in Excel to compute values for hundreds of thousands of rows of data, Excel will start freezing or showing "0" values.
The add in function allows me to ...
2
votes
1
answer
953
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
vote
2
answers
104
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 ...
0
votes
0
answers
32
views
I am trying to merge two tables in excel, but one of the tables comes out as a single column. How can I get all columns to appear?
I am trying to merge two tables:
After I click "OK", I get this:
Any idea what is happening here?
0
votes
0
answers
872
views
How do I add a blank column in Power Query Editor where I can input data in on the excel worksheet?
When I add a blank column with values "null (Number from Notes)" in power query editor to my existing data and load it to the worksheet, I'll type some data into the blank column while in ...
0
votes
1
answer
65
views
Every students' score plus their group score in Excel power pivot
There are students' scores based on their usual performance (scoreName), and group scores based on their group performance (scoreGroup). The total scores equal to their 'scoreName' plus their own ...
0
votes
0
answers
26
views
Excel Powerquery Import, keep custom column stick to table
Let's say I have a export excel file that looks like this:
Order No.
Name
Quanity
1
Bart
2
2
Lisa
1
3
Homer
5
I import this table using powerquery and maybe merge it with some other tables.
No I ...
0
votes
0
answers
75
views
Getting user names from a Sharepoint.files() extraction (Power Query)
From Sharepoint, I am able to extract a table containing filenames, file extensions, and dates...
https://learn.microsoft.com/en-us/power-query/connectors/sharepoint-folder
However, I would like to ...
0
votes
2
answers
222
views
Create a double entry accounting table by separating transactions
I have an Excel table with accounting transactions that are stored on a single line:
Date
To Account
From Account
Amount
Type
2023-06-01
2192
2281
$100.00
Transfer
I'm attempting to convert that ...
0
votes
1
answer
489
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
53
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 ...
0
votes
0
answers
84
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 ...
1
vote
1
answer
93
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.
0
votes
1
answer
204
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-...
0
votes
1
answer
134
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)="</...
1
vote
1
answer
369
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 ...
0
votes
2
answers
119
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
270
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 ...
0
votes
1
answer
361
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
...
3
votes
2
answers
9k
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 ...
0
votes
0
answers
415
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
46
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 ...
0
votes
1
answer
87
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 ...
0
votes
2
answers
1k
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 ...
0
votes
2
answers
471
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 ...