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

Filter by
Sorted by
Tagged with
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 ...
Scott's user avatar
  • 1
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 ...
newt335's user avatar
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 ...
leandro_shin's user avatar
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 ...
omega's user avatar
  • 195
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 ...
Nicholas Adams's user avatar
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 ...
Brtrnd's user avatar
  • 13
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 ...
doraemon's user avatar
  • 157
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 = {...
doraemon's user avatar
  • 157
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 ...
provocative's user avatar
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 ...
ecc450's user avatar
  • 1
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 ...
Dave's user avatar
  • 1,019
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, ...
Dave's user avatar
  • 1,019
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 ...
techtheatre's user avatar
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 ...
Cameron McMains's user avatar
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 ...
Dave's user avatar
  • 1,019
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 ...
Dave's user avatar
  • 1,019
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:...
Dave's user avatar
  • 1,019
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 ...
Dave's user avatar
  • 1,019
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 ...
Dave's user avatar
  • 1,019
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 ...
Dave's user avatar
  • 1,019
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 ...
user1605665's user avatar
  • 1,089
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 ...
Dave's user avatar
  • 1,019
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 ...
Ami's user avatar
  • 1
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 ...
Look's user avatar
  • 21
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 ...
Dave's user avatar
  • 1,019
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 ...
anon1212's user avatar
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 ...
Dave's user avatar
  • 1,019
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 ...
Dave's user avatar
  • 1,019
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?
anon1212's user avatar
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 ...
anon1212's user avatar
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 ...
likelee's user avatar
  • 113
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 ...
Rudolf's user avatar
  • 1
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 ...
FZNB's user avatar
  • 101
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 ...
dav's user avatar
  • 10.3k
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 ...
Arghadeep Modak's user avatar
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 ...
bigsim's user avatar
  • 1
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 ...
RoobyDooby's user avatar
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.
Liero's user avatar
  • 61
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-...
PerryW's user avatar
  • 162
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)="</...
User1974's user avatar
  • 137
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 ...
User1974's user avatar
  • 137
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 ...
Green Grasso Holm's user avatar
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 ...
GNG85's user avatar
  • 3
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 ...
Eva Travis's user avatar
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 ...
Alex's user avatar
  • 664
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 ...
hdfs dhiue's user avatar
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 ...
siri's user avatar
  • 3
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 ...
User1974's user avatar
  • 137
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 ...
Jason's user avatar
  • 1
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 ...
baskar's user avatar
  • 115

1
2 3 4 5