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
1 answer
21 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 ...
user avatar
0 votes
0 answers
39 views

In Mac OS, can I create an Excel file that has a data connection to a Microsoft SharePoint List and can can modify queries based on it?

My company uses the Microsoft suite extensively and typically on PC laptops. I am currently in a trial for MacBooks. For some work processes, we collect data into SharePoint Lists. I would like a live ...
user avatar
0 votes
0 answers
19 views

Excel PowerQuery date change not behaving as expected

hoping you can help me solve this problem. I have an Excel file downloaded from a database and I'm using Excel 2016 (yeah I know). The date column comes in as 'general' format displaying as Thursday, ...
user avatar
  • 1
2 votes
2 answers
93 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 ...
user avatar
0 votes
1 answer
30 views

Delete Specific Rows from Excel from a List of Row Numbers

I have a worksheet with 83,188 rows and 4 columns of data. I have another worksheet that has a list of row numbers (eg 18751, 9127, 696, and etc) that signify the rows I would like to delete from the ...
user avatar
0 votes
2 answers
53 views

Excel 365: Is there a way to fully protect (hide or encrypt) the url provided for a Data Source?

I'm importing data to Excel 356 using a data source "From webb". The url provided includes an API key that I do not want to share with others when I share the Excel file. I can use Protect ...
user avatar
  • 11
0 votes
1 answer
39 views

Convert from table to matrix format with Excel 2010

I would like to convert this data from table to matrix format where the names in the dest column become columns and the number values corresponds to the total but the origin column stands still origin ...
user avatar
0 votes
2 answers
75 views

How to add Epi Week aka MMWR Week column in Power BI

My dataset has a date column. I need to assign the correct Epi Week/MMWR Week to each row. I can't do it by hand because the dataset spans five years and has over 18,000 rows. The Power Query function ...
user avatar
0 votes
1 answer
155 views

List.Sum in Power Query is not ignoring null values

I added a column with this code: = Table.AddColumn(#"Changed Type", "Total Quantity", each List.Sum( {[species_males]+[#"species_females-mixed"] })) List.Sum is ...
user avatar
0 votes
1 answer
55 views

Extracting all matching (or close to) values between given START n-th and END n-th values / using Power Query

Consider having two tables. Table1(T1) has Date/Time and Pressure value columns and Table2(T2) has also Date/Time and Info values columns. T1 is showing pressure change through time, for instance: 21....
user avatar
  • 121
0 votes
1 answer
114 views

How to use Get Data in Excel without loading the data fully in Excel

I have a minor nuisance regarding loading the data into Excel. I load data from a SQL database as follows: first go to the tab Data > Get Data > From Database > From SQL Server Database. ...
user avatar
  • 161
0 votes
2 answers
454 views

"Speak Cells on Enter," not showing in Excel Options -> Quick Access Toolbar -> All Commands

Where is, "Speak Cells on Enter? Hopefully the attached screenshot says it all. "Speak Cells on Enter," is not showing up in Excel Options -> QAT -> All Commands. Anything I need ...
user avatar
0 votes
1 answer
31 views

Excel 365 - Dynamically importing data into excel from a Database

I have a spreadsheet with 40k rows or so which is added onto every week. Each row has an Employee ID, Employee’s Shift Number and Hours worked column (see attached image for sample data). Sample data ...
user avatar
0 votes
0 answers
31 views

MS Query optimization

I do have an excel workbook which fetches data from a Query (Call it Qx). Now, this query is linked to 150 other queries (which in turn are linked to xml files and these files changes frequently). ...
user avatar
0 votes
2 answers
69 views

Creating multiple values from single value + monthly increment

looking for any advice/ tips that could help with the creation of this dummy data set From this table, want to create another table ID MOB date(dd-mm-yyyy) A01 2 01-07-2019 A02 3 01-02-2019 This ...
user avatar
  • 1
0 votes
1 answer
143 views

JSON conversion in Power Query - how automatically get list of object property names

This is a question based on comment in Excel: Power Query - how to repeat block of code for each row in a table. Let's say we have a JSON with array of object each with N properies: { "data&...
user avatar
  • 820
0 votes
2 answers
333 views

Excel: Power Query - how to repeat block of code for each row in a table

I have JSON in format like this: { "data": [ { "date": "2021-01-01", "data": 10 }, { "date": "2021-01-02", ...
user avatar
  • 820
1 vote
1 answer
68 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 ...
user avatar
  • 161
0 votes
1 answer
618 views

Power Query issues after initial run of the query

I have a fairly simply power query built that collects data from 12 sheets in a single workbook and consolidates them into a master list. The data/columns are consistent across this workbook The ...
user avatar
  • 192
0 votes
1 answer
19 views

powerquery filtering data in import time

I have a data file I want to import into excel alpha,customer,10 alpha,town,12 bravo,customer,6 bravo,cars,5 charly,cats,5 (actual file is about 15 columns and 1000's lines) I can import into excel ...
user avatar
  • 1,667
0 votes
1 answer
97 views

Transforming ugly survey data in Excel / Power Query

I have input data from some surveys that requires some transformations. I've been playing with Power Query Transpose function, but I am clearly missing some important knowledge to do the required ...
user avatar
  • 3
0 votes
0 answers
38 views

Get user licence details from MSGraph in Power Query

I'm trying to pull user licence data into Excel with Power Query i.e. Joe blogs has M365 E3 and Power BI. I'm close, I'm using MSGraph as my data source (because there's a lot of other stuff there I ...
user avatar
  • 162
0 votes
0 answers
47 views

Transform and concatenate multiple data files into one worksheet in excel

I have multiple data files in csv format. I want to do a few simple operations on each and collect the summary of all in one excel worksheet - so I can easily see the results for all the csv files in ...
user avatar
1 vote
1 answer
52 views

Can Power Query be adjusted to only target one sheet?

I have several thousand excel workbooks. Each excel workbook has three sheets named "site", "survey" and "project info". I want to merge all of the "survey" ...
user avatar
  • 143
0 votes
0 answers
23 views

Excel power query authenticates fine on home page but not on API

I'm trying to extract data into Excel from a rest api, on a website protected by SSO (SAML, Azure AD). When using "Get data from web", Power query authenticates fine on the base domain name, ...
user avatar
0 votes
1 answer
32 views

Excel Power Query - M Language - Reference to "ThisTable"

As total Power Query newbie I'm trying to create Power Query function which adds an Index Column to the table. But I'm struggling with passing a table reference to AddIndexColumn function What I tried:...
user avatar
  • 820
1 vote
0 answers
236 views

Excel (2013) Power Query: [Expression.Error]: The key did not match any rows in the table - what files(s) is source of this error

I have tens of Excel files with similar table (means: same name, same headers, different content). I'd like to merge all data from these tables to one big table for future processing. I did it many ...
user avatar
  • 820
0 votes
1 answer
68 views

Rename or expand a variable number of columns with PowerQuery

I have a data table that looks like below: Report Date | ID | Percent 11/06/2021 | 12345 | 0.9 18/06/2021 | 12345 | 1 25/06/2021 | 12345 | 0.85 02/07/2021 | 12345 | ...
user avatar
0 votes
0 answers
31 views

Power Query to extract JSON parameter values from the data

Hope you are doing well!...I am trying to split the different parameter values for just the correct ,added and removed codes at a vbillid level..In the attached excel file the column codemap has the ...
user avatar
0 votes
0 answers
25 views

Utilse table list for headers in another table in Microsoft Excel

I want to create two tables in Excel: Table 1 - Contains a list of tasks and a description of the task and possibly some other information on the tasks (eg type, ownership, etc.). Table 2 - Contains a ...
user avatar
  • 123
0 votes
1 answer
163 views

SQL QUERY not picking up EXCEL date parameter not functional

I have an overnight data set that needs refreshing everyday. I used today's date with format =NOW() and tomorrow's date as =NOW()+1. The query is not pulling up the date from cell reference despite ...
user avatar
0 votes
2 answers
440 views

How to use Power Query to import multiple CSV files into a single Excel worksheet as worksheet columns?

I have a recent version of Excel (but very little experience with Excel). From a folder containing many .csv files, I want to import a few of the files as columns to a single Excel worksheet. I ...
user avatar
  • 123
0 votes
0 answers
44 views

Matching 2 Data Set rows with similar column headings

I have 2 data sets A-Q (left) and U-AA (right) I am trying to match the rows on the (right) U-AA with the rows on the (left) A-Q Once a record on the right matches the first like record to the left ...
user avatar
0 votes
0 answers
79 views

VBA - IBAN validation not working

I have a list of IBANs in Excel and wish to validate them via scraping data through VBA from this website: https://www.iban.com/iban-checker. In column A (cells A2:A3000), I have the IBANs and in ...
user avatar
3 votes
1 answer
1k 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 ...
user avatar
0 votes
0 answers
74 views

How to apply Text cell format permanently on a google sheet's column?

I recently added IDs as Number in a column those are basically built as alphanumeric. Then I've applied Text cell format on those IDs to keep their data consistent with the other cells in that column, ...
user avatar
0 votes
0 answers
42 views

Excel: Product ID's stored as text (example: "ABC123") causing problems with inner joins

I have a Product ID column that I'm trying to do an inner join on. The problem is, the Product ID column is alphanumeric, containing both letters and numbers. Some are purely numbers as well. Examples ...
user avatar
0 votes
1 answer
184 views

Microsoft Excel Power Query to Get Access Data , Based on cell value

I need to export MS Access data to MS Excel by Power query Here I need to filter ID by cell value A1 This is the query Source = Access.Database(File.Contents("C:\Users\Bas\Documents\...
user avatar
  • 113
0 votes
1 answer
151 views

Keep Helper Column Functions on Excel Table

I have a combined query from Power Query that I put into a table on a spreadsheet. On that same spreadsheet I add helper columns with a function. The problem is when there is no data for the query, ...
user avatar
  • 247
1 vote
1 answer
110 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 ...
user avatar
  • 604
0 votes
1 answer
34 views

Power Query and national formats

How to define national formats during PQ load? I have csv files being imported into Excel 365 and not all of them follow common US format. I need to define for file (or column) if it uses comma or ...
user avatar
  • 157
0 votes
1 answer
59 views

Combine columns from one table in one column in power query

I have a table (example). X - Some value. Num A1 A2 A3 B1 B2 B3 C1 C2 C3 1 x x x x x x x x x 2 x x x x x x x x x 3 x x x x x x x x x I need to make a ...
user avatar
0 votes
1 answer
522 views

Excel power query refresh locks up source file. Complete Excel shutdown required to unlock

The client file has a power query to a source file. Source file lives on a server drive. If the query is refreshed in the client file, and the source file is opened afterwards, the latter cannot be ...
user avatar
1 vote
1 answer
80 views

In Excel, is there a way to combine specific columns from different worksheets into one worksheet by appending them to the end of the previous one?

My office has about 8 worksheets that track dates following the process of their specific projects. There are 6 dates of interest that are common across all of these worksheets, but since every ...
user avatar
  • 11
0 votes
1 answer
135 views

Excel Power Query - How to Scroll Faster when Reordering Columns

Self explanatory. When you click & drag to reorder columns, if you have to scroll left or right it is a very slow process. Is there a faster way of doing this? Either to speed up the scrolling or ...
user avatar
  • 263
1 vote
1 answer
1k 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 ...
user avatar
0 votes
1 answer
94 views

How to split the column and merge them into one row(or one column) using power query?

There is one column with several rows which I want to convert into one column after split. Each cell of the column contains several values which can be split based on commas as separator. But I feel a ...
user avatar
0 votes
2 answers
110 views

How do I add a column to a Table in a row with data from another column?

I have a query that aggregates MySQL.Database output. What I would like to do is update the Table in Data to contain the value of Schema for each row in the Table. I think it would be something to do ...
user avatar
0 votes
1 answer
89 views

How to append a generated table from a different wsheet to another generated table by filtering through a conditional in Power Query?

I created a power query, where my source data is from division A (wsheet 1) of the company accounts wbook, applied my PQ steps, and generated a table. I will then split that generated table based on 3 ...
user avatar
  • 31
0 votes
1 answer
130 views

Power Query - Create a custom column that returns the latest value of another row by matching two columns

In power query, I want to create a custom column that returns the date only if: Column B equals "Endorsement" or "Cancellation" takes the account number of that row, matches it by ...
user avatar
  • 31