power query

Hi, I have 15 Excel reports sourced from SharePoint. The Source.Name column captures the report name which has the date mentioned. Please advise how I can create a Report Date column that only captures the date from the Source.Name column so that I can hide the Source Name column before loading...

Hello; I am cross-posting this question from SO. I'm using an API (for a project management database) that returns a result in the following form (in JSON format): {'Content': { 'Projects': ['project1', 'project2', 'project3', 'project4'], 'InvoiceGroups': ['group1'...

Dear All, I need your help in regards to the problem I am facing in Power Query in Excel. I have a folder where every month I save monthly Transactions Report. Then I combine them using Power Query to get YTD Report. All the columns names are same and it was all going well. Today I saved the...

I have a workbook with sheets for each financial year from 2010 onwards. All column headers are the same across all sheets. I need to be able to combine all the sheets into one sheet *BUT* I need this combined sheet to change with the others if rows are added/deleted in any of the other sheets...

Hello Excel Gurus! I have a challenge which I cant' quite tackle using Power Query but I'm sure there's a genius out there who can! My problem is as follows: 1. I have a "Colors" column which contains a list of concatenated colors 2. I want to split them by column and then dynamically rename...

Hi, I've cells on two rows which are merged, however, some cells are not merged and have different values/data. E.g. I want to bring Row 14-15 in one row also and delete the empty line and merge the comment section. While I import the table in power query, it shows one row as nil value, so...

Hello all. I am struggling to figure out what is going on. I have a power bi file that is pointing to a folder, that contains over 70 .csv files. Each file is around 200 Mb. I have been working with the power query editor all day. I finally had what I *thought* was a finished product...

Hello. I am new to using Power Query and have gotten stuck on something and am looking for some help. In a data set I would like to categorize the values in one of the fields using some kind of If/Then/And/Or logic. I can figure out the logic if I was using an Excel formula, but I am...

I have a data table that I query from one shared department spreadsheet. I pull that data into my spreadsheet with Power Query, I don't perform any special steps, just import. I then reference that DATA[Wire 1] and DATA[Wire 2], for example. If I put a column [Wire 1.5] in between [Wire 1] and...

I inherited 5 excel files between them have >100 individual pivot tables across multiple worksheets. Some sheets have multiple tables. I’d like to extract the file name, worksheet, cell location and most importantly, pivot table setup conditions (filter selections, rows, columns & values) for...

Hello all, I have a problem working with Excel using Power Query to bring in 79 files with a total of 113MB. These files are .xlxs files averaging 1.4 MB. The files are located on a folder on my desktop. When I go through the power query process, I open PQ, then select the folder, Transform...

Hi, If I am in Excel I can load a table to power query close add to data model , then go to power pivot, check I have what I want and if it is not right or I have forgotten to do something I can go back to power query make the changes I want and they will be reflected in the power pivot table...

Hello there, Although this is predominatley a Power Query Question, the criteria are obtained from an Excel Sheet and the output placed on 1 Excel sheet. I have some PQ code (Windows Excel 2016) that will load a single pre-specified CVS file into Excel as follows: Excel Sheet1: Excel Name...

Hi, I have the following table and my goal is to have to restrict the number of keywords per key to 3. For every key containing more than 3 keywords, I want to delete every keyword after the third one...

I don't see a lot from Salesforce/Power Query questions here but I have the following M in Power Query to pull data in from Salesforce custom table 'SR__c'. The table has a nested relationship table named 'RO__r' as placed in bold below. When I try to expand the relationship table ('RO__r'), it...

As long as there are matching fields in the source tables, we can join the associated queries (one-to-one, one-to-many, etc.) by using the Merge feature. We are going to use the following sample source tables to demonstrate parent & child merging. These tables have a parent-child relationship...

I have a PDF that has three pages - each page has a header and detail and the last page has an additional footer. I'd like to use Power Query to import the PDF tables into Excel I ran the File ->Get data ->From File -> From PDF and it came back with 6 tables and three pages I'd like to import...

1. How to get the positions of the first text char in a mixed string? AB7Files NamesOutput (position of first text char)81112. Orders692451 2 . Refunds910#784&@ . Cancellation9B8B8=SEARCH("O",A8)-1B9B9=SEARCH("R",A9)-1B10B10=SEARCH("C",A10)-1 2. How to use these splitter functions with...

I have 2 workbooks that I have linked together. One takes 2 massive csv files and makes them show ready for the second file. I am currently working on cleaning these files up to make them quicker and cleaner, plus merging in some more from other files. However, when needing to make changes in...

I want to split each row in Order Table into 2 separate rows where the quantity does not match the quantity given in Refund Table and further mark them as "Partial Order" and "Partial Return". With the help of the unitary method, I want to split their values as well. (refer to the result table)...