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
0answers
37 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 ...
0
votes
0answers
17 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 ...
3
votes
1answer
119 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 ...
0
votes
0answers
42 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, ...
0
votes
0answers
30 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 ...
0
votes
1answer
90 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\...
0
votes
1answer
50 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, ...
1
vote
1answer
23 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 ...
0
votes
1answer
31 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 ...
0
votes
1answer
35 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 ...
0
votes
1answer
149 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 ...
1
vote
1answer
73 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 ...
0
votes
1answer
18 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 ...
1
vote
1answer
402 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 ...
0
votes
1answer
36 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 ...
0
votes
2answers
104 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 ...
0
votes
1answer
83 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 ...
0
votes
1answer
65 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 ...
0
votes
0answers
62 views

Cannot connect to OData with Excel 2013 without PowerQuery

I need to connect to Odata sources using Excel 2013 in an environment where we are not able to install PowerQuery on most user machines. Excel 2013 has an option for getting data from Odata feeds, but ...
0
votes
1answer
269 views

Excel Power Query integration with Salesforce: The Service Returned an invalid token

I have set up Salesforce integration in Excel to pull data from some of my reports to a spreadsheet. After a Microsoft Windows Update (I suppose) my excel integration stopped working returning an ...
0
votes
0answers
69 views

Use Power Query to call API that requires Auth & share excel file to non-technical colleagues

I am using Excel2010 Power Query From Web to call an Auth-required API which returns data in JSON. Since this API requires Auth (username and password) to access, I set it in excel's Data Source ...
0
votes
1answer
47 views

Power Query: Change source folder via drop down list

Before I start to explain - I am not providing any data or else, because I need an idea first, to see if it is possible (if it is easy). Imagine you have one folder with X different subfolders inside, ...
0
votes
2answers
111 views

How to unstack data with more than two columns into several columns?

I have a table that contains three columns displayed in picture 1 and I want to change the table layout into the one shown in picture 2 by power query. Usually, the table comes with more than 500 PO ...
2
votes
2answers
84 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=*&...
1
vote
0answers
325 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 ...
0
votes
1answer
21 views

Excel Power Query Editor: Reformat the text in each individual box of a column

I currently converted a list of roughly 1200 items (1200 rows) and a problem arose when I looked at the date of each individual item and realised that the day and month was before the year which meant ...
0
votes
1answer
577 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 ...
0
votes
1answer
70 views

Designing a mass attendance system with Excel and PowerQuery

I am building an Excel spreadsheet for tracking a large number of personnel's daily attendance records. All data examples shown here are fictional, including the data structure. Only the gist of the ...
1
vote
1answer
49 views

Excel Query to Append Repeated Columns into New Table As Records

I have a Microsoft Forms that customers fill out. The same three questions are asked multiple times as they have to answer the same three questions for multiple parts. What ends up happening when I ...
0
votes
1answer
85 views

In powerBI, using a measure breaks the relationship between two tables. What am I doing wrong?

I am doing membership statistics for an organization. In our membership_subscriber table, we have the the dates when the membership begins and ends. There are multi year memberships. I need to show ...
0
votes
1answer
38 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 ...
0
votes
0answers
115 views

Excel VBA: Refresh Power Query table containing a UDF by VBA leads to overflow error but refreshing it manually does not

I have a table called PortfolioTbl that is outputted from a power query like this: In it, columns with black texts come from Power Query, columns with blue text are Excel formulas. One of the Excel ...
0
votes
1answer
70 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 ...
-1
votes
2answers
44 views

How to convert to editable table without losing web connection to query or losing manual date

So basically I am having a table with the columns S.No, Name, Lvl, ALvl, No of Creds, Creds Req, Max Creds, ACreds, Rating, Cred Collected. The only things which I get from website are Name, Lvl, Cred ...
0
votes
1answer
28 views

Excel 2016 Power Query - Expanding a fixed list like you do a record

I am using Power Query to Get Data from a json file. The elements in my json file that come up as "Record" I can expand the fields inside, all good. But some of the elements come up as "List". Now ...
0
votes
1answer
447 views

Use Excel Power Query to return multiple values from XML files

I have about 50 XML files that are structured the same. I want to extract multiple values from each one, with the values being in different "rows" of the XML table. Right now, I'm able to use Power ...
3
votes
1answer
571 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 ...
2
votes
1answer
179 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 ...
0
votes
2answers
104 views

Excel : list of installed base

Thanks in advance for helping me out! How would you advise me to go from this table: Equipment Desc | Ship To Name ------------------+---------- Small | Bob Small | Dylan ...
0
votes
0answers
54 views

How to Add custom Categories to Transaction Descriptions in an Excel Data Model

TL;DR: (How)Can I create a pivot table from a data model in Excel where I can add custom text to that table, and then feed that modified pivot table back into the Data model? Background: I am new to ...
0
votes
1answer
40 views

Add new data to existing Power Query

Sorry, this is probably a really basic question , im geuessing. How can i use an existing power query on as new excel file? i have tried going to Data > From File > Excel Workbook. It asks me create a ...
0
votes
0answers
287 views

How to refresh query table on Excel for Mac v16.31?

I created a spreadsheet on Excel on Windows 10 with a query table connected to other tables in the same spreadsheet. However when this file is opened in Excel for Mac, refresh will not work. Nothing ...
0
votes
1answer
402 views

How to add new CSV columns in Power Query?

When you edit your query of a loaded CSV file, it only shows the columns you chose when you first created the query. What if new versions of this CSV file have new columns that didn't exist when you ...
2
votes
0answers
12 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 ...
0
votes
1answer
19 views

Excel PowerQuery - Dimension to Column

I've got following 2 Tables: Date Name Value 1.1.19 A 11 1.1.19 B 54 1.1.19 C 234 2.1.19 A 346 2.1.19 B 654 3.1.19 A 156 3.1.19 B 6432 3.1.19 C ...
0
votes
1answer
71 views

How to add previously deleted CSV columns in Power Query?

After you use Power Query to load a CSV file, it only shows the columns you chose when you first created the query. What if you suddenly want to start using a column you previously deleted in the ...
0
votes
1answer
371 views

How to append tables in Power Query while merging similar columns which have different names, types and order?

I have 2 tables with similar values but different names, types and order of the columns. What's the best way to merge them into a single table? I believe the 2 options are Append or Merge with Full ...
0
votes
0answers
382 views

Can Power Query prompt for a file name when loading a text file?

Power Query can be used to pull text files into Excel. But does it have an equivalent to Excel's External Data's "Prompt for file name on refresh"? That is, to assume the pulled text file has a ...
0
votes
1answer
345 views

Values not pulled by Power Query from specific column in Excel table

I have copies of a standard-formatted data table in multiple sheets in my Excel 2013 workbook that I am using Power Query add-in to pull data from and compile into a larger table. Each summary table ...
0
votes
1answer
500 views

Using Excel Power Query to create new columns based on indentation of text

What I am trying to achieve I think is best explained with some pictures. I have the following example data set: Raw Data Using column1 I need to create two columns, the first will contain the ...