power query

  1. GraH

    Power Query matching each element of a list of terms with a free text field and returning list postion or list value

    Hi all, My data is structured in 2 Excel tables: tComments and tKeyTerms. Both are loaded in Power Query and I converted the tKeyTerms to a list {tKeyTerms}. The goal is to detect if any of the terms, which are not single words but a group of words, are used in any of the comments. When there...
  2. Z

    Analyse Salary Received and Amount Invested Year Wise and Month Wise

    I have 2 table in an excel workbook. 1. Consists of Month wise salary received by an employee for a span of 5 years Date NET PAY Basic Basic Arrears Adhoc Pay 30 April 2015 0.00 0.00 0.00 0.00 30 May 2015 0.00 0.00 0.00 0.00 30 June 2015 0.00 0.00 0.00 0.00 30 July 2015 0.00 0.00...
  3. R

    Power Query takes ages to load JSON data from web

    Hey everyone, I'm pretty new to Power Query and am having some trouble when it comes to loading times. When I refresh all data in the workbook (which is just the queries) it takes 7 minutes (I got a little frustrated and actually timed it haha) for everything to load and for me to be able to...
  4. R

    Unable to automatically pull data and perform custom modifications to a table from a query whose source is a dynamically changing link

    * I posted this on the excel forum, but since this also relates to power query I also am making a post here. Hey there, I am trying to make excel get automatically get data from a link (however the link changes based on certain inputs like ticker symbol, time intervals, to/from time) instead...
  5. P

    Stuck, Need Help (please). Trying to export unstructured text from PDF to import into Excel separating/placing text into specific columns

    Hello, I'm hoping someone can help with this work project. I have experience with formulas and pivot table and am somewhat familiar with Power Query but apparently not enough for it to help me with this work project. I've been trying to find an Excel work-around for this project to simplify it...
  6. B

    VBA and Corrupted Workbooks: How to prevent this from happening?

    Hi everyone, I apologize for the long post, but I've run been running into an issue that's a complete mystery to me and am asking for some help/advice. Another team at my firm owns a massive workbook saved down in a .xlsb format (it's massive and the size had to be cut down from how it was in...
  7. B

    VBA - Create Power Query with source and name as variable from Excel sheet

    Hello, I am currently trying to create a Power Query with Excel VBA. I have stored the name and the data source of the Power Query table in an Excel sheet. I now want to start the makro for creating an Power Query table but the makro should read the name and the source of the data from the Excel...
  8. T

    Power Query Refresh Problems

    I have a power query table that shows all my assets due throughout the year. At some point the PQ stopped working, even when I manually input dates into the fields. The Refresh button macro still works but I'm not sure if its updating. If assets due in 4th Qtr are updated to somewhere in the...
  9. T

    Get data from Analysis Services database

    I'm relatively new to Power Query in Excel, but I think this is a simple question. My company puts sales information in a large pivot table format and it looks like it's an Analysis Services "cube." Trouble is, Excel has trouble dealing with the data loads, and I'd like to connect to the...
  10. M

    Load cumulative data from Web .csv data Query to Excel table for a different Parameter Date each time

    Hi all, I hope everyone is keeping well during these difficult times! I have an Excel Power Query question and I hope someone is able to help me! I created several connections in Power Query which are currently 'connections only' within Queries & Connections. All data is being imported from...
  11. C

    Power Query Trim All Table Dynamically

    I have a bunch of text files with columnar format but with 90% same data but there are differences. I have done a bunch of steps on them applying dynamic transformations when appropriate because they are not all the same data. The final step is to clean all the whitespaces (Trim) but I am not...
  12. M

    Partial Transform

    I have a table of orders where the items in the order are all on one line. I need to have each item on its own line along with the order number and date. Some orders could have up to 253 items but of course, I do not want to transform the ones that are blank. Power Query got me very close but...
  13. D

    Power Query Slow Update

    Hi All I'm a PQ newbie and have been learning to do my tasks with the help of the internet. Thank you for countless times you all have helped. For this particular project, I've basically solved the problems using the answers I found on the internet. The issue is that it's taking too long...
  14. P

    Browser out of date

    Hi, I am trying to grab data from e.g. this URL: USD255087S - Spoon or similar article - Google Patents by using Get Data > Web Query. When trying to connect, there is no data available, and the Web Preview tab in the query says my browser is out of date. Under the hood, excel...
  15. L

    Power Query - Have to hit refresh two or three times for data to update correctly

    Hey all, I have to click refresh a minimum of twice to get my data tables to update correctly. Is there something I am missing in terms of how the queries are being executed? Is there a checkbox somewhere that I need to check or uncheck? Generally speaking, after the first refresh the data...
  16. K

    Convert Time into number for Payroll calculations in PowerQuery [Office 2013]

    Hey guys. I am doing my first Power Query project using and have successfully transformed data are I wanted that I could not do in PowerPivot. However I need to convert the hours worked by each employees into Number so that I can take that number and use it in the payroll software to calculate...
  17. R

    Power Query M formula language or Formula in Power Query

    Dear All Master , Please help me so that I do not use excel formulas such as in columns C, D, E, F that I marked yellow and I have a custom column in the power query namely PQ-ITEM NO NEW, PQ-GROUP SUPPLIER FIX, PQ-YEAR, PQ -DESCRIPTION so I want the results to use the power query formula or M...
  18. D

    Power Query Remove Duplicates with Condition

    I have a query to load files from a folder creating a list of files to work with. Some of these files end with .prg and some with .lib. Many of them appear in the list with both extensions. I would like to remove the .lib version of those instances. When I run "Remove Duplicates", Power Query...
  19. D

    "Unexpected error" on Load from network folder

    Hi! I'm trying to build a reporting tool via Power Query that combines data from multiple .xlsm files in a folder. Excel "Get Data -> From File -> From Folder" looks to do just that. After getting familiar with the concept on my local drive I tried to load the same files from a network drive as...
  20. A

    Dont seem to have access to Load Settings options in the Power Settings Pane in Power Query

    Hi All, I am working through a tutorial on Power Query and it is asking me to disable "Load to Worksheet" check box in the Power Settings Pane... however...i dont seem to have this...not for the workbook anyway... I can access the Load Settings options for the Global Default Settings but i...

Some videos you may like

This Week's Hot Topics

Top