+ Reply to Thread
Results 1 to 14 of 14

Power query question

  1. #1
    Forum Contributor
    Join Date
    12-30-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    143

    Power query question

    Hi

    is there a way in power query to fill down multiple columns based on conditions for ex

    it should fill down all columns in the query till it finds the letter a in column 1

  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 question

    Can elaborate on what exactly is it that you are looking to do? It would help if you can upload sample workbook, that has your starting data and expected/desired output.

    1. Do you want to filter table to show rows until first row with "A" in a column is encountered?
    2. Do you want to fill custom column with some value until above condition is met?
    etc etc.

    You can upload sample workbook by going to "Go Advanced" and scrolling down to "Additional Option", "Manage Attachments".
    ?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 Contributor
    Join Date
    12-30-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    143

    Re: Power query question

    OK so i have item # in the first column and in have other info in other columns like price size etc..

    what i'm trying to accomplish is as follows i want to fill down all other columns( where there is a null value) but i want it to fill down only till it finds the letter a in the first column

  4. #4
    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 question

    Then one way I can think of is as follows. Though there are multiple ways that you can go about this.

    1. If you don't have index column, add it to the table.

    2. Create copy of the table, to avoid circular reference error.

    3. Using copied table as reference, use following to obtain first row where column 1 contains letter "A". I named this "myFirstRow"
    Please Login or Register  to view this content.
    Note that it is case sensitive. Make sure it matches, or just transform entire Col1 to UPPER and use upper case always.

    4. Then back in original table, add custom column with formula like below. Or just use string/value you want to use instead of true/false.
    Please Login or Register  to view this content.
    You should be able to take it from there. If not, as I requested, upload sample file and I'll help.

    EDIT: Alternate to creating second query. You can split query after index column has been added.
    Last edited by CK76; 10-24-2017 at 02:43 PM.

  5. #5
    Forum Contributor
    Join Date
    12-30-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    143

    Re: Power query question

    HI

    i don't understand step 3 is it a custom function on the copied table

    also my original table doesn't recognize "my first row" and how does it help me with the fill down
    Last edited by AliGW; 10-25-2017 at 08:32 AM. Reason: Unnecessary quotation removed.

  6. #6
    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 question

    Not custom function, just a query used to return single row value from copied query/table.

    Upload sample like I asked and I can set up sample steps. You can easily follow steps in PowerQuery editor.

    I don't feel like guessing at your desired result and going back and forth on each steps applied. :p

  7. #7
    Forum Contributor
    Join Date
    12-30-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    143

    Re: Power query question

    ok im attaching a sample file
    Attached Files Attached Files
    Last edited by AliGW; 10-25-2017 at 08:31 AM. Reason: Unnecessary quotation removed.

  8. #8
    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 question

    Looking at your file, it's not readily apparent what transformation you are after.

    Are you looking for something like Table in Sheet1 of attached?
    Attached Files Attached Files

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,409

    Re: Power query question

    @Boruch - please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

    For normal conversational replies, try using the QUICK REPLY box below.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  10. #10
    Forum Contributor
    Join Date
    12-30-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    143

    Re: Power query question

    HI

    yes! that is what i'm looking to accomplish with power query exactly like table in sheet 1

    how is that done ?

  11. #11
    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 question

    Here's the steps. This example removes null row (i.e. where you added "A"), if you want to keep it, just modify custom function and remove ([#"ITEM #"] <> null) condition.
    EDIT: Removing condition won't just add back null rows. It will fill down on those rows as well. Instead, add step to before adding custom column to original query (replace null with "A" on Item #).
    Then change custom column formula to check for "A" rather than null.

    Steps:

    1. Add index column to query table
    2. Add custom column with following
    Please Login or Register  to view this content.
    3. On the custom column above, used Fill UP operation. This creates grouping of sample separated by null in Item#.

    4. Created custom function, and named it "myFillDown"
    Please Login or Register  to view this content.
    5. Add blank query and in source formula enter following. This will bring in distinct list of Group numbering.
    Please Login or Register  to view this content.
    6. Convert the list to table.

    7. Add custom column and use "myFillDown" custom function created earlier. This will add column with table as it's value
    Please Login or Register  to view this content.
    8. Expand the column excluding [Index] and [Custom]. Result will look like below.
    0.JPG

    9. Remove Column1 and change column data types. Load to your desired location.

    See attached sample as well.
    Attached Files Attached Files
    Last edited by CK76; 10-25-2017 at 11:04 AM. Reason: See EDIT:

  12. #12
    Forum Contributor
    Join Date
    12-30-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    143

    Re: Power query question

    thanks a lot that worked perfectly

    i was just wondering is there a way to shorten your custom function and not have to list all individual columns ({"col1","col2","col3","col4","col5","col6","col7","col8"}"
    is there a way to just say all columns, without actually typing them ?

  13. #13
    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 question

    Yes, there is. But most of the function is generated using UI. So it serves little purpose, unless number of columns change often.

    Don't have access to PQ at the moment will see if I have time tomorrow.

  14. #14
    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 question

    Ok in your sample, it's simple enough. Since all columns other than col1 to 8 does not have null, fill down using entire columns list won't impact the result.

    So myFillDown custom function becomes...
    Please Login or Register  to view this content.

+ 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. Sub-Forum for Power Query & Power BI?
    By AliGW in forum Suggestions for Improvement
    Replies: 7
    Last Post: 08-26-2018, 05:25 PM
  2. error in power query & power pivot
    By Baldev Kumar in forum Excel Charting & Pivots
    Replies: 8
    Last Post: 01-03-2018, 01:34 AM
  3. [SOLVED] Power Query
    By AMayland in forum Excel General
    Replies: 1
    Last Post: 10-03-2017, 02:30 PM
  4. SQL -> Power Query SQL
    By happydays886 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-14-2017, 11:56 AM
  5. Power Query question
    By BamBamMoneyBags in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 11-08-2016, 02:43 AM
  6. VBA and Power Query
    By Brawnystaff in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 05-16-2015, 05:10 AM

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