power query

  1. bfreescott

    Create Table and populate with Power Query Connection

    Hi guys I cannot figure out how to Load my query data to a table. I've established the connection and pulled the data, but the last step of creating and populating the table is eluding me. The recorder is no help since it simply refers to Selection. Anyone have a code snippet of doing this...
  2. T

    PowerQuery Error after reopening workbook "Authorization Error: The credentials for the source Web invalid."

    Hi All, I'd like to get the spotify web api data from the following link thru Excel PowerQuery web connector: Spotify API link I created a web query with the following code: let Bron = Json.Document(Web.Contents(functionGetNamedRange("SourceURL"), [Headers=[Accept="application/json"...
  3. nburaq

    VBA or PQ or Formula or Pivot Table?

    Hi Gents, I would like to compare two sheets having 3 common dependent in each sheet and would like to see the difference at another sheet or in sheet2 specified column as "Difference" I tried Pivot table and xlookup but didn't work for me and I think this one needs VBA or PQ. I even don't know...
  4. D

    Power Query Refresh gives old data

    Hi all, I've searched around but can't find any instance where this problem has been solved. I've got some queries set up in Excel (2016) that get stock data for the day from a website. Typically I'll open the workbook and hit refresh all, and the data loads. Today, though, it's loading...
  5. N

    Power Query ; imported text data with no spaces

    Hi all, I am new to power query, I'm currently having a problem importing data from PDF files.( i.e. the imported text has skipped all the spacing and output one string) What I get from import: | Description | PLASTERINGANDPAVING;Applyingwhitepaint; NipponPaintZeroTecPaint; | green...
  6. B

    Combine multiple Excel files into 1 then refresh the query

    Hi everyone, I have been having a really hard time to figure out what I am missing. The goal: I have need to allow multiple business to fill out an Excel sheet privately then have that data flow into a single master sheet that I maintain. This is to overcome the constant copying and pasting...
  7. G

    Power BI - Changing Value For Reports Based on Month/Year

    Hi Everyone, In example below we see John Doe recently changed managers. We know John changed managers on March 1st of this year. Normally the easiest way to input this change would be to simply go into our excel file that is being used as the source for the dataflow and change the manager’s...
  8. A

    Power Query Table Consolidation & Data Validation List Values

    I have a workbook with several sheets. I have a power query that consolidates the values from those disparate sheets into one consolidated view. On each of those sheets is a column "Pass / Fail", and the values of that cell are derived from a dropdown data validation list. When the query runs...
  9. M

    Power Query split?

    I have a table with a column that has full paths/categories on it: BaseCategory/SubCategoryA/SubCategoryA.1/SubCategoryA.1.1 OtherBaseCategory/SubCategoryB/SubCategoryB.1 How can I split the paths/categories using Power Query so that I can get all the previous categories as well: Full...
  10. J

    Queries and Connections opens in single column that cannot be widened

    In an older thread, this code was recommended to open "Queries and Connections": Sub OpenQueriesPane() Application.CommandBars("Queries and Connections").Visible = True Application.CommandBars("Queries and Connections").Width = 400 'Change width as suits. End Sub This works sometimes for me...
  11. R

    How to Get Queries/Connection Name Using Excel VBA?

    Hi Everyone, I kinda lost here, how to Get each Queries/Connection Name and put it in a cell? Currently I use this to refresh each of the connection, and I want to put each connection name in a cell after it refreshed. can we do that? '3. Refresh all query connections...
  12. G

    Power Query -- Fill down based on grouped index

    Hi all, This is in some ways a follow-up to an earlier thread. I was able to create a grouped index number to repeat at every N rows (which effectively partitions my groups), but now I am trying to figure out how to create a column where the first entry in the `Location` column of each `Group`...
  13. G

    Power Query -- New column fills down every Nth row of existing column

    Hi, I am working with this dataset that comes in this one-column/unstacked-type format, as shown on left in photo. I think my first step should be to split out a separate column listing location, as shown on right. Basically that means I need to tell Power Query to fill down based on every...
  14. B

    Loop with Power Query

    I'm discovering the power of Power Query, I'd like to convert this loop from xmlhttp This code is an example With Worksheets("alfa") For r = 5 To 100 id = .Cells(r, "A").Value If id <> "" Then With httpReq .Open "GET"...
  15. S

    Run multiple Power Query refreshes in sequense

    I have an excelfile with multiple queries. I would like to avoid running all at the same time (“Refresh all”) because it takes too much time and create some conflicts. I want a marco that runs one query then waits until it has finished then starts the next query. Any suggestions?
  16. K

    'M is for (Data) Monkey' Chapter 5 help

    Hi! I just recently started learning about Power Query through the book M is for (Data) Monkey. I'm a bit stuck on Chapter 5, page 46 on 'Consolidating Ranges and Worksheets'. (I'm using Excel 2019 by the way) I've already added print area ranges to the Jan, Feb, and Mar worksheets. I've done...
  17. D

    Power Query - COM-Error due to month & year extraction from date

    Hello everyone. For some reason, I keep getting an error, when I try to add two columns (year and month) based on the value in a "date" column. The error appears once I try to establish a "Data-connection" and add the data to my Data Model. I apologise for the language below being in Danish...
  18. D

    Use table to edit values of another table in Power Query

    TABLE A SiteId date Value 1 Value 2 Value 3 1 1-1-2020 5 20 500 1 2-5-2020 6 60 100 3 1-1-2021 2 85 250 5 1-1-2020 6 14 566 TABLE B SiteId date Value2 1 1-1-2020 25 3 1-1-2021 73 How do we update values in Table A using the Values of Table B and keep the rest as they are?
  19. C

    Power Query: Convert electives into a number of requirements completed

    I am developing a report for a training program that has a series of electives. The completion data from our learning management system has roughly the following format: Person Name Phase Course ID Completion Status Jane Doe 1 101 Yes John Smith 1 101 No John Smith 3 301 Yes Jane Doe...
  20. L

    Power Query - run a query on demand?

    Hello, I am new to Power Query and need some help please. I have an excel spreadsheet Source and a second excel spreadsheet Output generated from a Power query. The power query takes the data from the Source, filters by column Change number (only displaying rows with Change number) and has...

Watch MrExcel Video

This Week's Hot Topics

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back