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
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
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
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
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"
Note that it is case sensitive. Make sure it matches, or just transform entire Col1 to UPPER and use upper case always.Please Login or Register to view this content.
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.
You should be able to take it from there. If not, as I requested, upload sample file and I'll help.Please Login or Register to view this content.
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.
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.
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
ok im attaching a sample file
Last edited by AliGW; 10-25-2017 at 08:31 AM. Reason: Unnecessary quotation removed.
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?
@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.
HI
yes! that is what i'm looking to accomplish with power query exactly like table in sheet 1
how is that done ?
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
3. On the custom column above, used Fill UP operation. This creates grouping of sample separated by null in Item#.Please Login or Register to view this content.
4. Created custom function, and named it "myFillDown"
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.Please Login or Register to view this content.
7. Add custom column and use "myFillDown" custom function created earlier. This will add column with table as it's value
8. Expand the column excluding [Index] and [Custom]. Result will look like below.Please Login or Register to view this content.
0.JPG
9. Remove Column1 and change column data types. Load to your desired location.
See attached sample as well.
Last edited by CK76; 10-25-2017 at 11:04 AM. Reason: See EDIT:
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 ?
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.
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks