powerquery

  1. D

    scrapping from website

    Hello friends, I want to extract specific data (Table) from this website ( https://www.nseindia.com/option-chain ). I am attaching a photo for clarification. Pls help me it urgent. Thanks
  2. A

    VBA Error?

    Hi, I have the following VBA code: Set Wbk1 = ActiveWorkbook Fname = "Path name" Set Wbk2 = Workbooks.Open(Fname) Wbk2.RefreshAll DoEvents Wbk2.Sheets(1).Copy , Wbk1.Sheets(Wbk1.Sheets.Count) Wbk2.Close False So basically this code is part of a macro that opens ANOTHER...
  3. R

    PowerQuery in Excel 365

    Iin Power Query i don't have profiler in the View drop down. How do I enable that? I don't have column distribution either. Thank you.
  4. J

    PowerQuery - Pass Parameter from Cell Value replacing Variable from T-SQL Script

    Hi All, I am new to Power Query and I'm seeking some assistance. I have a script that I have created within SQL. Within the script, I have DECLARE'd and SET a variable for a 'Report Date'. This variable is used throughout the query and various date fields use this date. Previously, when I...
  5. F

    Loading .Net Framework Issue

    Dear All, I have a huge Excel file almost 30 MB with Tables and Macros. I used PowerQuery to automate it earlier but failed then I removed all quires from my file but it still stops and show a status "Loading .Net Framework..." when I open it which takes time. I want to know if Excel actually...
  6. S

    Run-time error '1004':[ Expression.Error] The name 'Source' wasn't recognized. Make sure it's spelled correctly

    I'm trying to add Power Query code to VBA but I'm getting an error that the "Source" wasn't recognized. The name 'Source' wasn't recognized. Make sure it's spelled correctly. I've been trying to do different spellings or playing with the commas but I'm still getting that error. Would need a...
  7. W

    Power BI / Query: Relationships error - after merging 2 queries

    Hi all, Here's what I have done: - I have merged 2 queries/tables - to create, let's call it, Query A - then duplicated Query A, selected Category column and then "deleted other columns" so as to create a "Category" lookup query/table. - I then deleted duplicates in this Category table. -...
  8. W

    Power Query - Fill Down with conditions

    Hi all, I have a profit and loss report that includes a Category column and a Sub Category column. I filled down the Category column with great success. However, there only exists a handful of Sub Categories in a data set with hundreds of rows. See pics below. The Sub Categories normally have...
  9. J

    % calculation with different total for each level of aggregation

    Good morning everyone. In my Excel file I have an "Orders" table which among other things includes the field "Hours needed", which is basically the time in hours required to complete the order and the "Date of order" which is the date when the order was carried out in dd/mm/yyyy format. You can...
  10. T

    greater/less than for dates in m language

    Hi all, I'm trying to create a column in power query editor that looks at a date and inserts 'exclude' if it's after 1Apr2020 and inserts 'include' otherwise. The editor, however, says using <= or >= doesn't work for dates. What expression should I use instead? Todd
  11. A

    PowerBI : Create a new table resulting from the analysis of another table

    Dear Community, I'm trying to move my excel skills into PowerBI for profesionnal reasons. Here is my case : I have a table of Non Conformity (NC) with creation and closure date columns. My goal is to know the quantity of opened Non Conformity at a given time. Exemple : "On the 1st february...
  12. L

    Difficulties in creating many-to-many relationships in PowerPivot

    Hey guys, I am currently trying to build a database in Excel that contains all Green Climate Fund (GCF) projects. Recently the GCF published an API that allows internet users to access their project data through the following URL: https://api.gcfund.org/v1/projects. I used the "Get Data from...
  13. P

    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 filenames). The connections are made to .xslx, .xlsm and .csv files. As I read, Microsoft states...
  14. R

    Powerquery - how to increase pivot drill-down beyond 1000 rows?

    Hi, With regular pivot tables it's possible to increase the double-click data drill-down too >1000 rows by clicking Data->Queries and connections->Right click on ThisWorkbookDataModel->Properties->and increasing the "maximum number of records to retrieve" under "OLAP drill-though". However if...
  15. M

    Power Query: Unstack data

    Hi Mr Excel Community, I would like to unstack this data using Power Query in order to have a clean list to pivot. Desired situation > To have a table with the following headers: Market, Items, Years, Month, Values. Change vs prior Year will be filtered out of the data sample. I know that I...
  16. M

    Power Query: Unpivot + Unstack 2 tabluar Tables into List

    Hi all, I need help on a data transformation assignment in Power Query. The goal is to unpivot and unstack data from two tabluar data sets and transform them into a list. Data Source = 2 .xlsx Files in Folder (please see data example below) Data Type = Tablular format with yearly dates on...
  17. M

    power query running total help

    I have a complicated windowed running total I'm trying to calculate. I've made an excel example that I'd like to implement in powerquery so I don' thave to copy the formulas down. Here's an example file https://file.io/k7yZ4D
  18. D

    Questions before diving in a cash management model using powerpivot and DAX

    Hi there, I'm refreshing/improving a cash management tool / dashboard I developed last year and I'm looking into information to do it the right way. The environment is as follow : - group has several companies and multiple bank account per company - some account are checking accounts and some...
  19. K

    Data transformation (using Formula) in Power pivot for data source from Folder

    Background: I've configured a PowerQuery to automatically fetch a sales and forecast dataset that updates monthly. So, the dataset gets arranged based on the Source File which gets refreshed on a monthly basis. Example: Feb'19 Dataset: Has Jan'19 Sales Actuals and Feb'19~Dec'19 Sales...
  20. B

    Excel / powerquery formula to compare dates/time when other value is matching.

    Hi all, I have a list of shipping numbers with piece numbers and date/time of first scan. Now i want to know when there is more then 1 hour between different piece numbers of the same shipping number (partial arrival). As i have more then 100k rows i would like to find a formula in PowerQuery...

Some videos you may like

This Week's Hot Topics

Top