power query

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

    Slow Excel browsing in Editing mode, but Protected View seems to be fast

    Dear all, I am helping a friend out, but I come across a problem that I am unable to solve. Consider the following file, I am unable to detect, why on earth the file opens extremely slowly and makes everything unworkable, like simply filtering and browsing the file. I have tried the following...
  9. K

    Excel Power Query: Custom Text String Formula

    Hi, I'm looking for some help to create a custom text string formula to convert this original date text string (20230926173000) into this exact format (2023-09-26T17:30:00.000). The original date text string is always this length. It new sting has to be this exact format. I could really use...
  10. T

    Power Query: transpose column to one comma seperated cell

    Hi I have a list of 4 digit numbers in a column in Power Query, for example: 1234 3827 3845 4028 3957 The file is refreshed with a new list each week with a differing amount of numbers. I want PQ to combine them so they end up in a single cell in the following format...
  11. S

    Self Referencing - Power Query

    I'm trying to get multiple tables to self reference to hold the information in place that I've manually added, somehow it's not working. I've scanned the website and come on to another tread with the same issue, and a recent YouTube doing the same, but unfortunately no success. Mr.Excel: Excel...
  12. T

    Power Query - Multi If

    Hi, I'm facing issue with formula in Power Query. I'd like to change excel formula from column G to power query but don't know how to write the code. Could you guys support? Sorry if information giving is not enough, pls let me know if any needed. Thanks in advance...
  13. C

    Power query duration not right

    When I import the duration for my data set it turns the 00 into 12 which is not the right duration . the minutes and seconds are correct but for some reason power query converts the 00: into 12:. Any help would be greatly appreciated.
  14. F

    Creating additional row in excel based on Cell Data

    Greetings, Ok so I'm trying to go from A to B....Need to split the data in the email address column, THEN, create additional line item.. Not sure if a formula would work, might have to try VBA...Please Help A Profile ID Request Type Change Type Email Address 1 Notification Yellow...
  15. S

    Returned items not in sales list?

    Hi, I'm trying to either with POWER QUERY or DAX find a way to go through my list of sold items by invoice number and look for items returned that were never sold to the customer. The item could be sold to another customer which is fine. But I'm looking for returns that are not valid as the...
  16. S

    Power Query looking for item not in list

    Hi, I'm looking to find items with returns (-1) units that were never purchased. Below is my power query that pulls all sales and returns. But I want to create a column that says the item returned (-1) was never purchased. Therefore does not exists in the table as a value of greater than 0...
  17. S

    Looking to find all sales that have a return.

    Hi I have a power query below that list all my sales. I looking to add a column that identifies any sales that have a returned item. Then I can see every sales with returns. let Source = Sql.Database("doff", "DB0000_171202195501"), dbo_tblSale = Source{[Schema="dbo",Item="tblSale"]}[Data]...
  18. J

    Power Query - Change column to row if columns match - Grouping

    Hi All. I am trying to create a spreadsheet which takes employee swipe times onto a site and gives how many hours they have worked. The format I have gotten it to gives me their name, date and entry on one row and then name date and exit on the second. But how can I combine these into a...
  19. S

    Extract date and specific string from text

    Hi I have the following table that contains everything in one cell. I want to extract the last date entered in that cell and size in the formate of (00/00"). That two columns should be added to the original table similar to pic below.
  20. S

    Convert table to list (Number)

    Hi I have the following code to convert sheet range to number in power query, The range named "Tests" would accept any thing except number. I know there is minor change I have to do, but I couldn't figure it out. let Source = Excel.CurrentWorkbook(){[Name= "Tests"]}[Content], MyList=...

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