power query

  1. 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...
  2. 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"...
  3. 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...
  4. 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...
  5. 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...
  6. 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...
  7. Q

    Defined Name use in Power Query

    Good day, I named a cell to "XYZ" and use it in Power Query as Excel.CurrentWorkbook(){[Name="XYZ"]}[Content]{0}[Column1] The question is, - if possible - how can I use a Defined Name in Power Query if it isn't refered to a range/cell but have an exact value? Thanks in advance!
  8. D

    Power Query datetimezone type

    Hi, I have a query with a column that looks like this: 2024-02-26T14:17:42.676231+01:00 If I change it to datetimezone type, it recognizes the parts properly and changes the data to this: 2024. 02. 26. 14:17:43 +01:00 However, when I load it to a table, the result is this...
  9. R

    Collecting data to an Invoice

    Hi I have a set of date from a table that i need to structure and merge in to a resonable overview. user shall only choose project and date from->to i have used Power Query and pivot to streamline the data, but the selection part is not easy i nee uder to choose project subproject date how do...
  10. A

    Table data not extracted - Power query

    Hello, I am getting data from an excel workbook within a folder Sheet has 3-4 rows at the top with couple of titles and blank rows, Then there is a table below these few rows. I am cleaning these few rows by removing them and then promoting headers from the table below. The issue is that some...
  11. L

    Error with making hour column from datetime column

    Hey everyone, I'm experiencing a problem with Power Query making a mistake in pulling an hour from a datetime. In the original table, the "Hour" column (2nd from left, in 24 hour format) is not there. I added that column by highlighting my datetime column in Power Query, going to the "Add...
  12. E

    Return most recent data - multiple criteria

    Hi, Bit of a multi part question as new to PQ and PBI. I have an initial table of data storing audits by location, type, date and the score awarded. There are lots of things I want to eventually pull out/ display from the data but two main things to focus on: 1) Finding the most recent score...
  13. D

    Power Query: Complex unpivot rows within table

    Hi, Im doing a time measurement of cycle times per operator. The sheet use to track the time for each name has title rows that describe crane , lane, location where the measurement was taken (see image is the area in the red box). This is done per each operator. I'm trying to unpivot...
  14. J

    PQ - Declaring Data Type in Defined Data Table

    I use Defined Data Tables in my Power Query Excel files so that I can updated the column headers to different names (they aren't uniform across sources). Like so: I bring it into Power Query Editor, M Code Magic, wham bam, I have consistent column header names. Wonderful. My ask: is it...
  15. D

    M code Occurrence

    Hi, can someone explain the difference between Occurrance.All and Occurrance.Repeating ? they both seem to to work the same and the documentation does not make the difference clear. Richard.
  16. T

    Pivot/unpivot help

    Hi Using PowerQuery I want to transform the first table into the second so that every 'Number' in a 'Region' shows every 'Location' in that 'Region', with multiple rows per 'Number'. This is just dummy data. I have tried doing this with pivot/unpivot but struggling to get my head around it...
  17. T

    Power Query: pivot column with text as value field

    Hi I want to use Power Query to pivot a set of data, but instead of the value cell being a number, it is text. I need to turn this: into this: This is just an example of how the data is setup, using PQ does work for this data, but when I try it with my actual data I get the...
  18. M

    A slicer or dropdown to return values where the selected value is found within

    Hi Everyone, I have been doing my research and have brought a table close to where it needs to be in power query. The last thing I need to know is if the following is possible. I am struggling to find a solution to this: Assumption: These distributor names need to remain concatenated...
  19. P

    Add Column and Fill Rows with the As of Date/Time on the Exported File

    I am using Power Query to combine CSV files from a folder. Each file has 2 "junk" rows which are removed at the Transform Sample FIle before the files are all combined. However I would like to grab the "As of" date/time from those rows and fill a new column with that info prior to removing...
  20. S

    Reconciling On Dollar Amounts Power Query

    Hello, I have two statements with hotel charges on them. What I need to do is take one from the credit card company, and the other one from the database and reconcile the amounts. I added both reports to Power Query, and I have Merged as new. This works perfectly to see the unique amount that is...

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