powerquery

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...

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...

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...

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...

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...

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

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...

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...

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...

I have a sheet that generates hyperlinks automatically based on other cell values using a custom column. It has been working fine until now, but I just started seeing some weird bugs in it. My custom column fomula is as follows: "'=HYPERLINK(""" & "URL<url>/files/J01009/" & [Account_Number] &...

Hi, all! I have a workbook with 12 spreadsheets, one for each year’s financial statements by month. They’re all formatted as financial statements. If I use Get/Transform to clean one sheet, can I use the PowerQuery “program” from the first sheet to clean the others? Or do I have to manually use...

Hey! First time here so apologies in advance if I'm asking this incorrectly. I have a pretty good basic understanding of excel but I've only ever really used it for some easy to moderate formulas and a few macros I've been helped with. I'm trying to figure out where to start with a problem I...

0 <button class="js-vote-down-btn grid--cell s-btn s-btn__unset c-pointer" title="This question does not show any research effort; it is unclear or not useful" aria-pressed="false" aria-label="down vote" data-selected-classes="fc-theme-primary" style="margin: 2px; box-sizing: inherit; font...

I have PowerBI connected to Excel data source (ie not imported but "linked"). This Excel (either can be on OneDrive business or desktop) contains a powerquery to do data transformation. The data in Excel data itself is stored in "Data" tab that resides within this Excel and does not further...

Hi all - I'm in the process of developing an MI dashboard for a set of business users, and I often get requests from these users for basic reports for the granular data. I want to build a self service reporting tool within this dashboard; The solution that I envisage is a worksheet with form...

Hi All, I've used the macro recorder to record the loading of a power query to table and I'm curious if the last line of code is redundant? With Sheets("Temp").ListObjects.Add(SourceType:=0, Source:= _ "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data...

I'm getting a new weird error on a simple "get a sheet from another local file" PQ query. "Typelib export: General Error. See IError info for more information (Exception from HRESULT: 0x80131136) I can't find that Hresult value on any board. Anyone know what's going on here? Client info...

Hello. May somebody help me with how to do this in Power Query. I'm using Excel 2016. I have the following 2 input tables. <tbody> TABLE 1 TABLE 2 DESCRIPTION VALUE PREFIX CODE ID 0 7235 ABX1 NAME JFMSC 3553 POWQ TYPE UHELQ 7459 UWEER DFRUL F4 10012 ABX1...

I am using Power Query and Power Pivot. I have a list of support tickets with dates opened and dates closed. I want a pivot table to count the number of tickets opened for a month and number of tickets closed for the month. When I create a slicer for each, it has to be two separate slicers. An...