query

  1. K

    Query Editor - Issue with coverting date format when year changes when date format is Day DD MMM e.g Sat 28 Dec

    https://imgur.com/a/2bFUSl2 At the switch over to 2020 it just shows as an error using the following #"Changed Type" =Table.TransformColumnTypes(Data0,{{"Column1", type date}, Is there an way to handle this?
  2. M

    Power Query not loading data - Stalling

    Recently I have had both relatively small power queries (180 rows, 10 lines of M code) and larger (approx. 6000 rows, 25 lines of M code) stall at the load stage. It happened first with the larger query and I thought it had to do with the size of the query. But it has now happened with smaller...
  3. M

    Importing files with variable number of columns

    I noticed something odd today and I don't know if this is new or if I just never encountered this before. I have 3 files in a folder and imported from the folder into Power Query. 2 of the files have 2 columns, 1 file has 3 columns. Power Query imported just the first 2 columns from each...
  4. M

    Power Query duplicate date error with no duplicates?!

    Excel 2016 64-bit - Suddenly my calendar file that loaded just fine is throwing errors that there are duplicate dates, so it can't join to the other DAX tables. The query appears just fine in PQ but blows up when I try to load to the Excel data model. These lines at the end don't make a...
  5. B

    How to updated statement with PtrSafe keyword

    Hi, I have posted a generic query regarding ‘PtrSafekeyword’ few days ago. However not able to find my post hence posting as new thread. I have below line of code in my macro and am required to update it with ‘PtrSafe keyword’ Private Declare Function GetPrivateProfileStringLib "kernel32"...
  6. M

    Formula.Firewall Issue

    I have an Excel file that uses Power Query to get data from two Access tables. The query is executed via the following VBA code in the Workbook_Open codeActiveWorkbook.Connections("Query - SourceFileName").Refresh ActiveWorkbook.Connections("Query - GoodsOut").Refresh The SourceFileName query...
  7. S

    Power Query - insert column by repeating single value from another query

    Hi, I have a power query qryTotalSales that simply has SalesAgentID and SalesVolume as an aggregate SalesAgentID SalesVolume 1 27 2 4 3 72 4 11 In another power query qryThreshold I have restricted...
  8. S

    Missing power query functions

    Hi, I have a Excelfile with a power query model in it. It works fine on one computer, but when I run it in on another computer there are errors. I use excel 2016 on both computers but the one with errors crashed a couple of weeks ago and got office reinstalled just the other day. The error...
  9. R

    Power query not updating

    Hi - I have a problem with power query not updating all data from my source file. My query was created from file / from folder, and I currently only have a single file in that folder at the minute. I have imported that data into power query with no issues currently, however if I change some...
  10. F

    Can't query all rows from website table where URL doesn't change with pagination

    Hello, I'm trying to do a simple web query for a couple of sites, but however their tables are coded, it only queries the first page of the table rather than all results. When clicking to the next page of the table (or selecting to view all rows), the page URL doesn't change, which eliminates a...
  11. J

    Date Difference

    I am sure this can be done, but I am not able to find anything specifically addressing it. Is there a way to create a DateDiff parameter in a query, where is it calculating the days, between current date and a date field within the table the query is built from?
  12. J

    VBA: Query refresh always runs first regardless of where it is in the code.

    I'm trying to create a macro that does the following: On theButton was clicked set theButton style =msoShapeStylePreset12 Run the query Query refresh completes set theButton style = msoShapeStylePreset14 end But, the button doesn't change until after the query has refreshed. I eve...
  13. S

    Need to understand if I need loop or something else to have DB react on input action

    So I have a certain table-list, which will have data populated or deleted sometimes. I also have queried raw data. I want it to add 1 more column when 1 more record gets added to table-list and delete 1 column if 1 record from table-list gets deleted (it doesn't have to be certain place where...
  14. A

    Ways to bring data from SQL server but need input parameters for SQL to read

    I was wondering if someone can please help me on best way to use parametirized SQL query and bring it to Power Query. I have access to SQL server with known connection string. SQL is long and needs to use specific input parameters (project numbers) in it. I don't want to have them changed each...
  15. D

    Power query - Updating files before getting values

    I have a query that retrieves information from multiple excel files in a folder The problem I am running into is that those files have formulas that depend on the current date Seemingly, when power query retrieves the data, it doesn't allow the formulas to update first This results in incorrect...
  16. W

    Sorting rows alphanumerically using a query

    Hello, I am working on a worksheet where I copy data from an Access database which then I want to sort alphanumerically; as in letters on top and then ascending numerical values. The query I'm using is: SQL = "SELECT Material, Description, Qty, Unit, Amount, Currency, Batch, PO " & _ "...
  17. S

    If #REF then load to Query If #VALUE then don't. Need help with loading #REF records

    Hello, Can you please help me find a workaround to load needed records Is there a way to replace #REF error, but keep #VALUE one ? Or in other way, Is there a way to load #REF records , but exclude #VALUE ones ? If it helps I use the following formula in order to find keywords. And result...
  18. S

    Walkthrough on How to Create a Combo Box with a subform query

    Does anyone have a walkthrough on how to create a combo box with a subform query that is placed into a form please? Trying to get the combo box to search two different fields in the form but it isn't working for me and I guess I need to start from scratch on this one?
  19. D

    Any way to pull in data from already-open web pages (without reloading them?)

    Goal: scrape data from ~300 web pages (of identical format), each with stock market data for a different symbol. I currently use VBA that uses Excel's built-in Web Query tool to loop through all 300 URLs, pulling the data from each as it goes. Problem is that it takes ~5 minutes, which I want...
  20. A

    Countif in power query

    Hello How do I execute the following code in Power Query? =IF(COUNTIF($B$2:B2,B2)=1,1,0)

Some videos you may like

This Week's Hot Topics

Top