+ Reply to Thread
Results 1 to 4 of 4

Power Query and imported table headers

  1. #1
    Forum Contributor
    Join Date
    08-30-2015
    Location
    Tresfjord, Norway
    MS-Off Ver
    365
    Posts
    169

    Power Query and imported table headers

    Hei ...

    I have this spreadsheet with a few hundred rows, contaning a series of tables for the net. Each with its own headers.
    The problem, it seems, is that those headers are halting my look-up functions in another spreadsheet.
    The formula that do not work is like this: =SUMPRODUCT((data!$D:$D=$D6)*data!M:M)
    The formula that works is like this; =SUMPRODUCT((data!$D$2:$D$200=$D6)*data!M$2:M$200)

    Is there a way to avoid header row in Power Query?

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Power Query and imported table headers

    Short answer. No. Query table will always have header.

    But can't you use Excel's structured table reference instead of static range in your formula?

    Ex: Say your table returned by PowerQuery is Table1...
    =SUMPRODUCT((Table1[ColumnDColumnName]=$D6)*Table1[ColumnMColumnName])

    TableName[ColumnName] will be range that holds data (i.e. excludes header)
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

  3. #3
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Power Query and imported table headers

    @Tresfjording

    Thanks for the rep

    If you question is resolved, please mark the thread as solved, using thread tool found at top of your initial pose.

  4. #4
    Forum Contributor
    Join Date
    08-30-2015
    Location
    Tresfjord, Norway
    MS-Off Ver
    365
    Posts
    169

    Re: Power Query and imported table headers

    Hi ...
    Just an update.
    If you need multiple tables in the same spreadsheet, you can combine them in Power Query.
    Works as a charm.

    Have a nice day.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. POWER Query and Power Pivot Table !!
    By haitham.shop in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 08-06-2018, 04:09 PM
  2. Power Query and Power Pivot Table
    By haitham.shop in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 08-06-2018, 06:17 AM
  3. Power Query and Power Pivot Table !!
    By haitham.shop in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-06-2018, 06:15 AM
  4. POWER Query and Power Pivot Table !!
    By haitham.shop in forum Access Tables & Databases
    Replies: 0
    Last Post: 08-06-2018, 05:59 AM
  5. Replies: 5
    Last Post: 06-29-2018, 05:35 AM
  6. Sub-Forum for Excel Power Tools (Power Query, Power Pivot & Power BI)
    By chullan88 in forum Suggestions for Improvement
    Replies: 10
    Last Post: 06-28-2018, 02:25 PM
  7. Denesting tables in XML data imported into Power Query
    By Cuine100 in forum Excel General
    Replies: 1
    Last Post: 08-11-2016, 09:30 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1