power query

Hi Excel Gurus, I created a spreadsheet... (tab 1) - Source Data (OLD data) (tab 2) - Source Data (NEW data) (tab 3) - Power query to combine (tab 1+2) Old and new data (tab 4) - I have created a pivot table based on the Power Query (tab 3). Now when I update the data in tab 1 & 2, the Power...

I am using power query to transform a data set. I have two sets of separate columns which need to be appended to one another, while copying over the data from other corresponding columns. This is what I currently have: ABCDEFGH1Employee IDEmployee...

Hi, I am new to power query and I have a hopefully easy question. I have a template which has a table and I have made power query look at it and make changes, thus creating a new sheet with those changes. Now if I want to add a new sheet which has a table in it just like the template but with...

Hello excel experts, I am trying to merge two profit and loss statements which contain the same list of unique properties but contain some different GLs. The two statements are an "Actual" and a "Budget", which is why there are differences at times. I would like my third query to merge the two...

Hello everyone, I'm trying to add a column that will sum up values in another column based on a few conditions. The code I currently have uses List.Sum. I have to do a similar conditional running total calculation several times in my query along with a few other transformations. The query...

Hello All, I have 3 files have different data formats and fields. There is one common field amongst the 3 reports. The Location ID. The three Files would have different details each of which are connected by the Location ID. First one, the employee register. Second is the Monthly Costing...

Hi folks, I'd be grateful for any help with what should be a really simple set of PowerQuery steps - it's driving me mad as I just cannot get it right. Rather than (embarrasing myself by) pasting my current set of steps, let me show you what I have to work with (obviously the input document is...

Hi I am trying to append multiple files into one sheet. The problem is that files differ slightly. The opening cells across the multiple sheets look like the attached. I try appending and combining and I keep getting errors. Should I use VBA?

Hi, Consider the following table, it is an exam record for students. There is a name column at the start and then the Semester Check column and Semester 1 till Semester 6 columns. I want to Find the missing represented by null, The failures (any value < .4) and Want to display the failure...

Hi Guys Please take a look at the picture below that should describe my situation. How do I go about adjusting my below Power Query Navigation expression that takes into account that the "Sheet Name" (i.e. Navigation expression) is variable each time the below excel report data and saved from...

Hi all, I'm struggling to figure this one out...I've searched online and have tried suggestions for other similar requests, but to no avail... In my data set, I have Survey IDs (unique for each survey), but trying to add a column (Survey #) that indicates what survey # it is for that...

Hi I have a folder with many excel files, I want to filter out all the cells with a background colour which are often the headings of table. From there, I want to copy and paste the heading(cell contents) into a new file. How do i use VBA/Power Query when the tables across the different...

Hi Peers, Need your guidance if possible. I have searched formums and google and cannot find a solution I dont know if this is even possible: I have the following code which works on a windows operating system. Which was taken from this site...

Table1 Name Others Person A I am interested in doing calculus and trigonometry Person B coding, making websites Table2 Column A Subject category websites, mobile application, .. etc Information Technology mathemathics, calculus, trigonometry Math Column A are all the different...

Hello Connoisseurs, I am trying to get a cell value instead of the absolute address in the code below. let Source = Excel.Workbook(File.Contents("C:\AAD\Sheet1.xlsx"), null, true), Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data], #"Changed Type" = ..... in #"Changed...

Hi Power Query Gurus out there! I don't have extensive knowledge of Power Query, but use it for simpler tasks. I'm currently using it to query data from a SharePoint list (not relevant to my question), and I'm looking to have PQ look at the data (sample data below), and randomly select 3 IDs...

The following is an example of text that appears in a header in the Power Query editor: 11/16/2023 Points Diff. The date is different in each cell. I would like to replace the cell value with just the date (e.g., drop the "Points Diff."). When I use Transform/Replace in the editor for...

Hi, This is just about general advice rather than a specific case, but when writing custom function, when do I need to use the variables first and then let and when within the the let statement; l let RunningTotal = (Avlaue, Aindex) => List.Sum( List.FirstN( Avlaue, Aindex ) )...

I want to check if each column value contains a substring of each value in a list, and output "FOUND" if true. Current Code: = Table.AddColumn(#"Source", "TEST", each if Text.Contains([Email Domain], RandomList) = true then "Found" else "Invalid")

Hello, I'm trying to take a text file with data in the following format. The data begins like this: OGRID: [373075] XTO PERMIAN OPERATING LLC. Reference ID: fAPP2226958316 Reference Period: 202301 Days Reported: 18 Total Flared: 14,355 MCF Total Vented: 0 MCF OGRID: [373075] XTO PERMIAN...