+ Reply to Thread
Results 1 to 4 of 4

Use Row/Index number as Variable in Power Query

  1. #1
    Valued Forum Contributor
    Join Date
    04-24-2014
    Location
    United States
    MS-Off Ver
    Office 365 ProPlus
    Posts
    853

    Use Row/Index number as Variable in Power Query

    I'm working to learn how to expand Binary Data out in Power Query, and trying to figure how to reference the row/index number in trying to make a function.

    I have something like this in power query:

    Please Login or Register  to view this content.
    And I can't figure how to convert the {0} to be a variable. It should be referencing either the row number or the index column. I created an index column starting at 0, thinking I could then reference that.

    But this doesn't work
    Content = #"Added Index"{[Index]}[Content],

    nor does this
    Content = #"Added Index"[Index][Content],

    Any ideas? Is this a syntax issue or something else?

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    MS365 Apps for enterprise
    Posts
    5,894

    Re: Use Row/Index number as Variable in Power Query

    Do you want all rows? Or specific rows?

    If wanting to expand all rows in a given table (query step)... You really don't need to re-invent the wheel. There's built-in dialog for expanding all binary data.

    If you look at [Content]. There should be double down arrow that initiates dialog for combining all binary data.

    For variable for Row. If it is single row, then variable can be simple integer.
    Ex:
    Please Login or Register  to view this content.
    If you need to hold list of rows. You need to use different syntax. Using Table.SelectRows.
    EDIT: This requires addition of Index column. There are other methods, but this would be most performant method of accessing specific row#.
    Please Login or Register  to view this content.
    Last edited by CK76; 11-06-2023 at 05:05 PM.
    ?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
    Valued Forum Contributor
    Join Date
    04-24-2014
    Location
    United States
    MS-Off Ver
    Office 365 ProPlus
    Posts
    853

    Re: Use Row/Index number as Variable in Power Query

    I'm probably not thinking this out logically in my head, or maybe over thinking the entire process.

    The built in expansion of the data (that double arrow) errors out trying to combine. I think that is because I'm attempting to unzip a zip file that has 50 excel files in it (not csv), and then also select and transform only 1 worksheet from each of those 50 files. I likely need to build and setup a function or two, just haven't figured it out yet.

    Attempting to use the build in function returns an error of "We didn't recognize the format of your first file ().

    I have a similar question open on the Power BI forums, and don't want to go cross/double posting. But I'll keep thinking more on it. It's probably much easier than I'm making it out to be.

  4. #4
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    MS365 Apps for enterprise
    Posts
    5,894

    Re: Use Row/Index number as Variable in Power Query

    Ok, I'm assuming you are using custom function like in the link below to Unzip and read contents of Zip file.
    http://sql10.blogspot.com/2016/06/re...erquery-m.html

    You will then need to interpret content as Excel.Workbook(). As base content returned will lack file type info for PQ to interpret. You will need to specify the type yourself in M code.
    Ex:
    Please Login or Register  to view this content.
    You will then need to filter the Excel.Workbook content for the specific sheet you need to query and expand out the data.

    Rest of the steps will depend on your data structure.

+ 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. Replies: 2
    Last Post: 07-14-2023, 08:21 AM
  2. [SOLVED] Power Query - replace file name and sheet with variable
    By Gregb11 in forum Excel General
    Replies: 3
    Last Post: 04-08-2023, 01:32 PM
  3. Power query using step as variable
    By Dicken in forum Office 365
    Replies: 0
    Last Post: 12-09-2022, 10:59 AM
  4. [SOLVED] Power Query - Concatenate multiple variable entries
    By DystopiaRelic in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-25-2021, 01:05 PM
  5. Power Query - Date List with changing variable
    By Steveapa in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-17-2020, 04:54 PM
  6. Power Query - Date List with changing variable
    By Steveapa in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-17-2020, 12:27 PM
  7. 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

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