power quary

I'm having a bit of an issue with an Excel power query and VBA. In VBA: I create a new sheet in my workbook and name it. I get my data from a CSV file with the information I need into a power query in my workbook. I can see the query in the workbook and view the data, everything is there. It...

Hi, I'm working on a pet project to keep track of my investments. Periodically I download the data into Excel to calculate tenure, IRR and XIRR which takes time as I have to clean up the data and calculate each of the funds separately. Now I wish to automate some of the steps involving cleaning...

Hi, Apologies, I know this is a very long-winded and convoluted post, but it's hard to put into succinct words. This query is both VBA related and Power Query related. I'm trying to loop through emails in an Outlook folder and save and rename all attachments. I'd also like to modify the code...

Hi there, I'm looking for guidance from a Power Query expert to assist me in designing and implementing a measure for stock analysis in Excel. Specifically, I need help calculating Closing Stock and Opening Stock for a calendar year. For Opening Stock, I'm trying to define a logic where the...

i have around 70 excel workbooks each employee has 1 for every month all identical and saved in there own folders under a master folder i need to pull the total average from every one , i can can them to show on PQ but its shows all the other values in that column which i don't need , any help...

Hello, I would like some help making the following Pivot table in a "power query". I have attached the final version of my file. Transpose is necessary, but due to too many titles, I couldn't. Picture of the table below. It will make the job much easier and simpler in a situation like the...

Hi I have the table attached and I want to have the output table attached. Power BI has to get the last owned vehicle by person and then get when it was owned.

In power query, I need to make multiple columns to build on an equation and I don't want 40 custom columns in my steps. How can I make my below equation run through one step (probably two for expanding columns) for 10+ columns? The columns called for a and b change FYI. =...

Hello, I'm using power query to compile multiple spreadsheets. I have added a custom column which I need to have an If/else statement. If my SITE column contains East and in the same row the NUMBER column contain 1 then enter "East1" This part is working but I have more data to check, else if...

Hi All i want to count every number 2 as 1,2,3,4,5.... Qty Result I want 2 1 2 1 1 2 2 2 2 2 3 2 3 2 4 2 4

Watching this video, I figured out how to set up a live connection to a Google Workbook through Power Query. Basically, you copy the URL for sharing, and then set up a URL with an ID gleaned from that URL. The ID is between the /d/ and the last / of the URL provided. The video is 2 years old...

There's a web site (Market Holidays for United States for 2022) that has a small table of the list of Market Holidays. Long story short, I was able to pull in all the tables from 1900 to 2100 using a Power Query function. In doing that I discovered that 56 years (including 2017) are missing the...

Hi I have a parameters PQ function which is used to dynamically renames column, which is working great, however I am having trouble using the parameter in a custom column formula to reference the column name, my objective is to create the custom column so i can use to filter unwanted records...

Hi, I have a weekly terms report that shows the weekly terms over the week. What I'm looking for is to find a way that can show the changes Week over Week without me copying every week's report into a tab, then build separate pivots for each week, and do manual calculations. Any recommendation...

Hello, I am converting PDF with Power Query and run into the problem. The conversion separates text into two rows for the column "details" and keeps the rest columns blank. The issue is not regular; sometimes it appears after every row, other times after couple of rows. How may combine this...

Hi, I would like my POWER QUERY to filter only a select date range. Sales from 730 days ago to 830 days ago. Sales from 365 days ago to 465 days ago. Sales from today to 100 days ago. Here is my current code: SELECT tblsale.sale_link, tblsale.loc_code AS [LOC#]...

Hi All, I have data that comes in tab format. so have to summary the data with Value & Reference numbers by client. but with multiple transaction ID's and reference numbers im not able to arrange the summary the way my management want. They need Client Name > Reference Number for Sales >...

Hi, I’m using a simple notepad .vbs macro to refresh all spreadsheets in the folder which is scheduled to refresh twice a day by task scheduler. The data source of the report is SQL server and hence I’ll have to leave OLE DB refresh option enabled to make sure the data remains up-to-date. When a...

Good morning, I am writing to you because I am completely blocked ! I have an array with formulas in Power Query ! The principle is simple : Get the difference between "cash buy (achat comptant)" and "cash sell(vente comptant)" values ! There is a file in history and a graph. I would like to...