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.
Hi Anne,
Got me stumped! There is no pattern that I can see that you can exploit. I think you need a lookup table that maps the departments to the part numbers. You can then merge the two tables and bring in the department on the same row as the invoice.
Mynda
Thank you for getting back to me. So it wasn't just me! Let me go and try that then. Any thoughts on why the Fill Down/Fill up wouldn't work in the CSV file (I hadn't removed the file name?) . Thanks again!
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
Hi Anne,
Fill Up/Down will not work until you have null's instead of blanks.
Before Fill Up/Down, Replace "" with null in the columns you want. (in the first field of Replace function do not type anything, just type null in the Replace with field)
Hi Anne
Is this what you are trying to achieve?
Sunny
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.
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
