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!

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

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

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

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

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

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

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

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.

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

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

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

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

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

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

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

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

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

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

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.