I have a sheet in which there would be a dynamic range with static columns and dynamic rows,
Range("A1:Y1892").Select **(need this range to be dynamic, which can be furthure used in power query)
Application.CutCopyMode = False...
since using Office 365, when i insert a Pivot for a query ( Insert - > PivotTable - > Use an external data source - > Selecting the query) , it gets immediately duplicated. As many pivot I instert the number of queries increase.
I am trying to build a more complex report with several...
I am trying to filter a column with the recent 3 Business days (Including Holidays and Weekends) based on Max date in the Column and the filter should also include the Blank Cells in the column.
For Example, the Column max date is 9th September and I would like to create a filter whichever...
I'm stuck on the best way to summarize date. I have 30+ Techs, they each have their own workbook set out as per, "User Workbook Example"
Each tech may do a different job each day.. they may do the same, however in there workbook the jobs are laid out as tabs at the bottom.
My Example here is...
Hi lovely People!
I'm trying to update my power pivot data model to keep a table with my measures but change a Power Pivot Data Connection to a Power Query Data Connection.
Using Power Pivot Data Model Connections in my WorkbookDataModel.xlsx
This is my table:
When trying to edit to get...
Hi, I have the following VBA code:
Set Wbk1 = ActiveWorkbook
Fname = "Path name"
Set Wbk2 = Workbooks.Open(Fname)
Wbk2.Sheets(1).Copy , Wbk1.Sheets(Wbk1.Sheets.Count)
So basically this code is part of a macro that opens ANOTHER...
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...
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...
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...
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.
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...
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...
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?
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...
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...
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...
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".