power query

  1. L

    Conditional Merge

    Good evening everyone, I had a question ... btw, excuse my bad english: I have a couple of tables, CONTRIBUTION and BILLING, which have an "ID" field with which I relate them (the relationship between the tables is * (CONTRIBUTION) -> 1 (BILLING)), as well as a CONSUMPTION (numeric) field , a...
  2. M

    Ellipses at the bottom of the table after CLose & Load

    I've never seen this before. Rather than loading the 200-ish rows of data from Power Query, only 5 rows loaded and then ellipses. What might be going on?
  3. MrExcel

    Splitting a CSV file into 205 individual one-character columns

    I was doing a seminar recently and an auditor had a need to split an ACH file into 205 columns, with each column being one character wide. Her current process: Use Data, Text to Columns, Fixed Width, and then click 204 times in the Data Preview Window of Step 2 of the Wizard. One idea from...
  4. G

    Find a 7 character code in text strings of varying length

    Hi, I've a list of item paths, each with a product code in them. The product code is always in the format AAA1111, but it's in a different place in each string, and each string is a differing length. Can anyone think of a forumla that will extract that 7 character code? I have 6,500 lines so...
  5. L

    Duplicate rows and fill in new columns based on master list

    Hello all, This is my first time posting and I am hoping you can help me out with this problem in Excel. I have a Final sheet which has locations repeated a fixed n number of times (in this case, n=2) and has its own property columns as shown below: <tbody> Location Property3 Property4 X...
  6. leopardhawk

    Get External Data (long shot question!)

    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to by 'Get External Data' every year on January 1 at 00:01 local time? The following URL is currently in use on a worksheet within my workbook...
  7. F

    How do I get Excel to return a value based on the max value in a table?

    0 1 2 3 4 5 1 1 2 3 4 5 3 6 7 8 9 0 5 1 2 3 4 6 7 2 3 3 4 2 I have a table as such with rows and columns in bold. I know the max value within this table is 9. How can I get Excel to return "3" and "4"?
  8. L

    How do i create this DAX table in Power Query

    I every one, i have a problem... I have created a table in dax, like this NISCountLT2 = SUMMARIZECOLUMNS(LT2[ID]; "Total"; COUNT(LT2[ID])) to count repeated IDs then i use this code to create a column with ranges for the report Bucket Range = SWITCH( TRUE(); NISCountLT2[Total]=1;"1"...
  9. N

    Power Query - Calendar Table

    Good morning - I have a fiscal calendar that my company creates which I pull into Power BI. Columns include date, fiscal year, fiscal week, day of week, work day, holiday, holiday name, week of month, and fiscal quarter. What I want to do is creat a few custom column in power query that will...
  10. F

    Power Query - split out lines of data in a single cell

    Hi, I have the following data in excel and am looking for a way to split it out into seperate lines - I was thinking Power Query could be the answer but not sure on the steps I could take. It is a one off data clean so I am also up for doing it in Excel via formula but again not certain on the...
  11. M

    Power Query get and transform data

    Hi experts, for reporting on e-commerce data I am preparing daily, weekly and monthly reports. The data sits in different databases (Oracle, Google Analytics and numerous third partner platforms). The KPIs needed are: order count, revenue, cost, clicks, impressions. Is there a way to automate...
  12. L

    Power Pivot Error "Many to Many" Error Is Incorrect

    I have a Dimension Table and a Fact Table brought in by Power Query. In Power Query, I added a Calculated Column to each table named "Report_ID". I'm trying to create a relationship in Power Pivot for these tables, but I keep getting the error message that Power Pivot does not currently support...
  13. I

    Power Query - Split Master Table into Multiple table as per value in a column (Dynamic)

    Okay guys, I did ask this question in many forums but did not get any reply. I have a salary sheet, where new data inserted every month. This is not a one time job, it needs to be done every month. Salary Sheet looks like this. (image 1) I want to print Salary sheet for each UNIT, with total...
  14. L

    Power Query - Custom Column - Reference Previous Cell

    I use a formula in my excel workbook that references the previous cell or cell above in order to calculate inventory based on order count. I have searched far and wide for a way to do this in Power Query in a custom column and have had no luck. Can anyone assist? I would like this in a custom...
  15. S

    If #REF then load to Query If #VALUE then don't. Need help with loading #REF records

    Hello, Can you please help me find a workaround to load needed records Is there a way to replace #REF error, but keep #VALUE one ? Or in other way, Is there a way to load #REF records , but exclude #VALUE ones ? If it helps I use the following formula in order to find keywords. And result...
  16. M

    Rename table headings using Names in another column.

    If I import a table in Excel PowerQuery, what functions and syntax do I use to reference another table for the names of the different columns?
  17. Kiss the Krogan

    VBA: Create Folder and Add Documents According to Excel Unique ID

    Hi Everyone, I've done some searching but am not getting anywhere with my requirements. I am hoping someone can help. I have a Power Query which identifies new members. These members need cards printed and there are two files the printer needs: the new member photo and the new member...
  18. L

    Power Query to Concatenate Rows Based on Condition

    Hi, I have column with Names, (unique) ID, and Address. The problem is some addresses have their street address in one row and the row below it is their city, state and zip. I would like to have their street address, city, state, and zip in concatenated in on line based on their ID. For...
  19. L

    Match multiple values from one column with single value from other column

    Dear MrExcel community, greetings! After thorough searching through forum, I could not find an answer to my needs (maybe I didn't search properly?) I have a following issue: I have a LOC column that has unique values (locations) and GRADE column (which has only 4 possible values). My...
  20. L

    Power Query - Dynamic Column Headers - By Date

    I import a file each day that contains about 40 columns of data. 15 of the 40 column headers are dates for the next 15 days from the current date. Therefore, each day the column headers change in the source data for those 15 columns. My refresh will crash each day since it cannot find the exact...

Some videos you may like

This Week's Hot Topics