

May 2, 2014

I've been experimenting with PQ on this attached QuickBooks file (dummy company). In the attached file (Column 1 and Column 1 - Copy) I want to have fill down of Air Conditioning, then Brakes, Electrical - your basic departments. Then in Column 1 - Copy I want to end up with Part numbers e.g. 120Y, K1212. Of course the Parts data is not consistently number. At this point I have tried: Duplicate Columns, Column from Examples, Add a suffix, add a prefix, Custom Columns plus the Fill Down/Fill up (that wouldn't work for some reason when I tried importing CSV from a folder) My (so far, not so cunning) plan was to see if I could have a folder with these QuickBooks CSV files and then with a file that would do all this cleaning for me. What am I missing? Thanks.

VIP

Trusted Members

June 25, 2016

Hi Anne
Although I don't have PQ, I am interested to know what is your expected result from your attachment.
It is not too clear for me from your description.
If you want to fill in the part number in column A beside the Invoice maybe you can try using the F5-Goto-Special-Blanks trick.
Cheers
Sunny

VIP

Trusted Members

June 25, 2016



November 8, 2013

Hi Anne,
First, the Total rows should be removed, you don't need them, as you will most probably create your own reports.
After you do this, a pattern will show up: if in Type column is "Invoice" and the 2 cells above it are empty (null), then the category can be taken from Column1, 2 rows above Invoice.
To accomplish this, we have to use a method that can read the row number, obviously based on an Index column.
After you add an Index column, I used this formula to add a new column:
= Table.AddColumn(#"Added Index", "Custom", each if [Type]="Invoice" then (if (#"Added Index"[Type]{[Index]-1}=null and #"Added Index"[Type]{[Index]-2}=null) then #"Added Index"[Column1]{[Index]-2} else null) else null)
Fill this new column down, remove any rows with null in Type column and that's it.
Sample file attached.


May 2, 2014

Thank you for all your help here - much appreciated. I did get it all working...What I did in the end had a certain amount of brute force about it (ahem!). I cleaned out the totals. Duplicated the column that had the department and then using a combination of Find and Replace to clean out individual entries - yes, I know, not great but it got the job done. 🙂 @Catalin Bombea - as you rightly said, I found that I had to clean out stuff to get it to Null and then the Fill Down worked for the invoices.
Most intrigued by your formula @Catalin and will have a closer look. THANK YOU ALL SO MUCH
1 Guest(s)
