power query

  1. P

    Power Query with Dynamic Sample File

    I have a Power Query where I need to reference two different Sample Files depending on a variable: let SourceType = Excel.CurrentWorkbook(){[Name="SourceType"]}[Content]{0}[Column1], SourceLocation = if SourceType = "Fileshare" then "C:\Fileshare" else "C:\Egnyte", Source =...
  2. A

    PQ Add Dynamic "Average" Column

    Hello community! I have a table of reports that gets updated every month. In the PQ editor I pivoted the report date column "REPORT PERIOD" which is in MM-YYYY format. And the data for it I used "Workload Data Input". I did that so it calculate s average of all inputs from each period for each...
  3. J

    Augmenting VBA to Refresh Specific before All Power Queries

    Howdy! I am currently using these two pieces of VBA to refresh my Power Queries and it works beautifully. But I was wondering if anyone is able to assist in augmenting it a bit. It currently produces the information displayed in this picture: My need is to be able to run anything that begins...
  4. C

    Query source folder contains too much info, the query breaks before it can load. What are my options?

    A team I'm working on used to manually process data received from clients writing out their PDF documents into an ever growing range of data within Excel. We only have access to Office 365 programs and Power Query has been a real game changer, but we're all pretty new to it. We've been...
  5. T

    Convert Inconsistent, Paragraph-Like Data to Columns & Rows for Import

    Help? We are moving records from one provider to another and their downloadable information is difficult to manipulate into a format suitable for import, and rightfully so. (Can you say Job Security?) But we ARE moving. Please see attached image. The data is paragraph-like...
  6. C

    Help me Convert, Merge and Transpose a pdf table into a singular row, and then repeat that step ad nauseam to create a database

    Context I have an ever growing collection of 200+ paged pdf documents that contain bank checks. The checks themselves will not scan into Power query, however their header's will. My goal is to extract the header of each check statement and then transform that information into a row that would...
  7. M

    Add "Name" Column in Power Query - Append

    When making a query with multiple tables, a column called "Name" is automatically created, containing the table name... When appending queries, however, it doesn't do this. Is there a way to add a "Name" column to my appended query, so I can see which query the data came from? My queries...
  8. Q

    Need help writing corresponding Powershell code in Power Query

    I have this code in Powershell and I would like to have the same code in Power Query. Grateful for any help. $uri = "https://graph.microsoft.com/beta/security/securescores" $request = Invoke-MgGraphRequest -Uri $URI -method GET -ErrorAction Stop foreach ($item in $request.value) {...
  9. H

    Dynamically pulling allowances and ignoring certain allowance for each employee

    Dear All, I have a file, in which the data tab fetches the data via Power Query in a table. There is second tab, in which we have to layout the data in a certain way to do some calculations for each employee based on the allowances they are getting. The complications are that certain allowances...
  10. shift-del

    Merge columns dynamically

    Hello I want to select columns in the worksheet and then merge them in PQ. The first part is done. The second part drives me crazy. I searched the web and the best I could find was this: Stackoverflow.com But it errors with: Expression.Error: Das Feld "NAME" des Datensatzes wurde nicht...
  11. S

    Power query refresh running unusably slow.

    Hi all, I have several data files in a standard format (xlsx files, 1 tab (all named the same), 4 columns of data each (all common headers(Owner, Supplier, Amount and Account)) - varying numbers of rows in each. These files are in a folder on sharepoint (no option to save these outside of...
  12. J

    Power Query - Custom Column Logic for Multiple Queries

    I have 2 data sources that come from the same application, but one is for Sales and one is for Invoicing. The columns are all named and formatted the same. There is a Custom Column in queries that use those 2 data sources where the logic is to be exactly the same...but sometimes out of sight...
  13. I

    PowerBI - User Date Prompt from SQL Server Database

    Hi all, i have a date written into the SQL that links to the the data warehouse data source. Each month the user currently goes into the source SQL and changes this date manually. Does anyone know if its possible to create a user prompt on the front end dashboard so that the user can amend this...
  14. L

    Add original hour column to accompany date column without datetime.

    Hey everyone, I am trying to add a new 24-hour time column to a one column table that contains only dates. The Date column is dynamically pulled from a database that provides dates for 30 days into the future. I am doing projection work, so there is no original time column for me to pull from...
  15. G

    Unstack dataset

    Hi there, I was wondering if you help me transform the following dataset? This is how it comes out of the system: EmpNum ProductName InputName EntryValue Product Entry ID Emp1 Apples Type GrannySmith 123 Emp1 Apples Units 5 123 Emp1 Apples Type Gala 456 Emp1 Apples Units 3 456...
  16. J

    A Sort on a Table.Combine in a Group By step...but from a different column

    I have 3 total columns, Customer Job ID, Daily #, and Daily Date. I would like to update this M code to combine all distinct Daily #s sorted in order by Daily Date. Any assistance on updating this would be greatly appreciated. = Table.Group(#"Filtered Rows", {"Customer Job ID"}, {{"Daily IDs"...
  17. I

    Multiples operations through Power Query

    Hi everyone, I want to make operations through Power Query. I am a rookie but I almost succeeded reaching my goal here but now I am stuck. I have 5 columns which gives for each technician, type of hours spent (work, holidays, ...), the number of hours spent, the date when it was spent and the...
  18. RICH937

    Power Query: Averaging values when greater than zero

    Hi folks. About 3 weeks ago, I began to try and learn how to use power query, and while I think I'm slowly getting the basics, I cannot for the life of me figure out how to do what would be an insanely easy task in Excel. I have a fairly large database that I'm working with, and am trying to...
  19. M

    Power Query to find name matches between two spreadsheets

    I have two files: bios v1.2.xlsx and departures v3.4.xlsx. Can Power Query compare the most recent versions of both files saved to the same folder and produce a list of names common to both? The exact match for comparison is: bios v1.2 -> ‘current’ tab -> “NameFirst”&” “&”NameLast” = departures...
  20. D

    Power Query refresh taking HOURS after adding custom column

    I am making a Power BI dashboard and the data refresh of the power query has become unreasonably slow. The Source files are just 4 txt files (stored locally and less than 5MB total) which combined are less than 17k rows, so not large enough to cause major issues. The refresh was ok before, even...

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
Back
Top