powerquery

  1. 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...
  2. 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...
  3. 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...
  4. 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...
  5. 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
  6. 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...
  7. 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...
  8. 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...
  9. K

    Weird bug in PowerQuery hyperlink generation?

    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] &...
  10. D

    Cleaning up data with Get and Transform

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

    Splitting Row in to Multiple Rows based on Column Value

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

    How to remove duplicates from individual powerquery columns without removing entire rows

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

    Can PowerBI refresh the PowerQuery on my Excel data

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

    SQL Query on PowerPivot Data Model through VBA?

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

    VBA Power Query Load to table refresh redundant code?

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

    New weird PQ error

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

    Get transformed table from 2 input tables PQ

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

    One Date Slicer for Multiple Date Fields

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

    Renaming fields in PowerQuery

    When I load an Excel sheet in PowerBI, I was attempting to rename the fields in PowerQuery by filtering out the fields and renaming the ones I want to by replacing the values However, I cannot retrieve the fields that I filtered out. What is the proper way to do this?
  20. C

    Removed query but still getting error on refresh saying it cant be found

    I removed a query from my model (using Powerquery and Powerpivot). I went through and made sure that query is not referenced in any PowerPivot formulas or any Powerquery coding. When I hit refresh it tells me it cannot find this query to refresh. I see when I go into existing connections the...

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top