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
-1
votes
2answers
35 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 ...
-1
votes
0answers
17 views

Powerquery Excel - DateTime format issue

We are fetching data from ODATA using Powerquery. Through we set column type as Date/Time, after every refresh, datetime format is getting changed. maximum records are showing correctly however lots ...
0
votes
0answers
19 views

Importing huge dataset held in POWER PIVOT table to Microsoft ACCESS

Background I am trying to make a database, but am having issues importing data into microsoft access. I have huge files, 4000,000 rows long and each one needed merging with much smaller tables. What ...
0
votes
0answers
9 views

Excel Power Query making accessive load requests from web source

My problem is that I have a seemingly simply web query under Excel Power Query referencing to two csv-sources. I frquently get locked out becasue of request quota being exceeded (5queries per minute ...
0
votes
1answer
25 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
0answers
21 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 ...
2
votes
1answer
43 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
66 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
73 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
28 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
37 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
102 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
0answers
222 views

Can't refresh external connection in Excel from Mac

I have a an Excel workbook that have several External data connections via PowerQuery (actually from built in Data > Get External Data; and slightly modified code in PowerQuery to include variable ...
0
votes
0answers
18 views

Adding new files to Power Query folder, are all old files read again?

I have a folder with CSV-reports, and I am using power query to load/combine and modify these into one spreadsheet. These files don't change. I am getting new CSV-reports in the same format weekly, ...
0
votes
1answer
95 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
10 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
18 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
33 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
0answers
14 views

Can Power Query easily change the number of cut rows?

Is there an easy way to change the number of cut rows? That is, without having to locate the applied step and/or messing with the code itself? In other words, like Import Text's UI. Compare to Text ...
0
votes
1answer
38 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
147 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
59 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
169 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 ...
0
votes
2answers
30 views

How to index date values into columns in Excel

I have the following table in Excel 2016: ID Type1 Type2 Type3 1 01.03.2018 null 01.02.2019 2 01.04.2018 01.05.2018 null 3 null ...
0
votes
2answers
101 views

Can I update / delete / insert database records via power query?

So I use Get & Transform (fka Power Query) in Office 365 / Excel 365 to query a MariaDB database (equivalent to MySQL in this case). Given that I have the database permissions to do so, can I ...
0
votes
1answer
213 views

Excel Power Pivot Data model Sum of Max date groupings

I have a dataset in Excel that I need to summarize. The source table is in a format like below: Table example The data extends for years and includes a heap more metadata columns. The value in each ...
0
votes
1answer
20 views

PowerQuery in Excel 'master' spreadsheet on SharePoint can only be refreshed by one user and gives authentication errors for others

I created a group of SharePoint libraries on an intranet that each contain an identically formatted spreadsheet, and another library that contains a “Master” spreadsheet that fills a table from the ...
0
votes
1answer
21 views

Subindexing and Marginal Differencing in Power Query (Excel)

I have this dataset: User Date Time A 9/1/2019 8:00 AM A 9/1/2019 10:00 AM A 9/1/2019 4:00 PM A 9/1/2019 5:00 PM A 9/2/2019 8:00 AM A 9/2/2019 5:00 PM B 9/1/2019 ...
2
votes
1answer
671 views

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....
0
votes
1answer
37 views

How to make a DAX measure NOT to re-calculate on aggregated levels?

I have a DAX measure which works perfectly on the level 0 POV in the Pivot Table but I was wondering if there is a way to make it aggregate like a regular number when filtering to top level POV (e.g. ...
0
votes
0answers
43 views

MS Excel 365 : Comma Delimited data to rows for multiple columns

I've comma delimited values in two columns like in the image here. Main data: After using power query and splitting them in rows , I'm getting data as: But the expected data format is: How can I ...
0
votes
2answers
229 views

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 ...
0
votes
1answer
207 views

Power Query Not Showing second decimal if zero

I'm having an issue in excels power query. I can not figure out how to always show two decimal places. For example, in excel it displays like this which is perfect 1.21 = 1.21 1.30 = 1.30 In ...
0
votes
1answer
29 views

Background Refresh Not Refreshing

I am pretty new to excel/power query, so I apologize if this is a basic question. I'm using an Excel 16 document on sharepoint that is pulling data from another sharepoint document. The data queries ...
1
vote
1answer
493 views

How to apply Conditional Formatting based on previous cell value in Power BI?

I would like to accomplish the following in Power BI, I have done it in Excel but I haven't been able to figure it out for Power BI. I have a Power Bi table that I need the values in rows to change ...
0
votes
1answer
42 views

Add missing values to power query result set based on criteria

I have a final result set from 5 Workbook Queries in Excel. One of the columns in this query has empty cells since during a query merge there were no matching values to fill. Now I'd like to create a ...
0
votes
0answers
198 views

Excel Power Query: [DataSource.NotFound] File or Folder: We couldn't find the folder

I have trouble using Power Query to import from a folder on a network location. It looks like the actual size of the folder I am accessing is much larger than what's visible to me (it's very likely ...
1
vote
0answers
34 views

Power Query and Excel don't agree on sorting?

Take a table that has the following records Field A | a If I sort it with Excel, I get the pipe character before the "a" Field A | a Now, if I sort it with Power Query, I get Field A a | That ...
0
votes
0answers
29 views

Excel pivot table - combining data from three columns. Power query?

My income is divided into three portions - cash, bank transaction, and USD. Let's say they are in columns A to C respectively. The total income is: total income = column A + column B + column C * ...
0
votes
1answer
42 views

How to create a histogram with 2 legends based on dates

I have a table that is created automatically from Power Query. It looks like this: | Event | Start Date | End Date | +--------------------+------------+------------+ | Champion's ...
0
votes
1answer
55 views

170+ columns, count number of rows with repeating (duplicate) data

I'm looking at a large data set, 170+ columns, 10k rows and want to summarize the data within the table by counting the number of times a certain descriptor is used. In the example screenshot below, ...
1
vote
3answers
75 views

Excel – Set cell values in a column based on another cell value in intervals

I have a sheet with datasets for weekly hours by store. They are all in one sheet separated by store (see the first Site: ‘ID’ number in the first row, third column).  Currently, in my file, the ...
0
votes
1answer
458 views

Power query pivot column with multiple records

How can I pivot a table where there is more than one record for a given column heading? See images below - I have transformed data so that it's listed by date, and I want to pivot the table so that ...
1
vote
1answer
2k views

Oracle.DataAccess.Client and .NET Framework errors in Microsoft Excel

When trying to access an Oracle 12c Database through Excel and Power Query, I received an error stating The recommended provider("Oracle.DataAccess.Client") is not installed. You can continue ...
0
votes
1answer
42 views

Power Query - Appended Queries pulling from same folder - Need only 1 query to run on new files

I have two queries created in Power Query that pull data from the same folder and are appended. The reason for this is because that folder contains many files with two distinct data structures, one is ...
0
votes
1answer
98 views

How can I use Excel's Power Query From Web option to extract news headlines from various sites, and keep the data in real-time?

I am familiar with how this tool can extract data from tables on websites like Wikipedia, but I've run into a few issues. Here is what I need to happen, if possible with Excel, or any other software: ...
0
votes
2answers
325 views

How do I compare two columns for matching and non matching values and get it in the format that I want?

How do I find matching and non matching values between two columns and format it in the below manner? Input: | expected | match | forward | backward | actual | |----------|-------|---------|---------...
0
votes
1answer
527 views

Load many csv files at once in Power BI

I received a relational database export where each csv file represents a table. Just 107 files, and i need to import them all to Power BI... Am i out of luck and need to import each of them ...
0
votes
1answer
245 views

OData Feed / Power Query in Excel 2016 Not Retrieving All / Duplicating Some Records

In Excel, I successfully connected to an OData feed from Data.Medicare.gov (the website is https://data.medicare.gov/Hospital-Compare/Healthcare-Associated-Infections-Hospital/77hc-ibv8/data and the ...
0
votes
0answers
269 views

Copy But Unable to Paste within a Excel Worksheet

I have two worksheets in an excel file in which I can't paste copied data within the worksheet. I click on the next cell after copy and the rotating dashes disappear or when I right-click the paste ...