power query question

Hi excel people,

Hope I’m in the right place here because I can’t find a powerquery section.

I’m doing a powerquery course but I’m way not finished yet.
Now I have an issue that I have to deal with at work very often and I'm pretty sure it can be resolved with powerquery. But I have no clue how at the moment.

Check the attached sheet. I exported 2 reports into excel from my accounting software.
(data is adjusted and numbers, totals and subtotals are incorrect.)

My accounting program exports data in this way constantly.
I exported 2 reports into excel from my accounting software.
(data is confidential but the numbers are all altered/way incorrect… so don’t mind the balances… they’re incorrect)

1. sheet is a general ledger export
2. is a paylist export

I need to change this data into a proper dbase.
1. get rid of the rows mentioning 'subtotaal'
2. adding a column and copy the 'cust# - customer name' on the records below that name. 3. deleting the original 'cust# - customer name' line (I reckon that's the same like #1)

Especially #2 I don't have a clue how to do that. That’s the tricky one for me.
Eternal grace to the one who can tell me how to resolve this. ;p

Much obliged,

Grts Jo

Alansidman,

That looks perfect!
Hooray... thx a lot. You're a life savier!

Dumb question I reckon (but I'm pretty new to this powerquery)
How do I apply these steps to the AP table?

So for now much obliged.

I can close the thread I reckon... but if you could answer this question it would be perfect!

grts Jo

Hi Alansidman,

I see what you did here... you've changed the AP and the GL into a proper dbase.
But I don't have a clue how you did that.

I've tried to copy & paste your code into the commandline of "changed type" several times... but that doesn't work at all.

- Could you please point out the menu path for instance to add new unprocessed records to the sheet where power query performs the tasks at hand?
- explain into a bit more details what to do with the code? Like paste it into the commandline of "changed type"? That seems to be the idea but I only generate mess and error code if I do t
- What are the menu-paths to 'generate this query myself'... where for instance can I find this "filter rows" or "Add Conditional Colum" or "= Table.FillDown(#"Added Custom",{"Custom"})" ?

Hope I'm not a to much of a 'pain in the ***'... ;p

grts Jo

Alansidman,

I've got the powerquery course from xelplus: https://courses.xelplus.com/p/excel-power-query
But lack of time prevented me from getting completely through it up till now.
But like I mentioned... dealing with these moronic exports from the finance system that would be great to resolve asap.

Your last reply is getting me a lot further though.
I'm getting stuck at #8 though: In the window type = Text.Contains([Datum],null) then [Factuur] else null

Isn't there nr. missing between 7 and 8 cause you didn't mention which button to use in the 'add column' menu... I reckon it's "invoke custome function" because it puts the new column in the front but I don't know how to get the formula in...

New column name = Custom
function query = Text.Contains([Datum],null) then [Factuur] else null.... I reckon but it doesn't late me paste there...


:o)

Aha... via the prior file I found out that # 6,5 should be: click button "add conditional column"
let's see where that gets me... ;p

Allright allright allright Alansidman! (to quote Mr. McConaughey ;p)

Figured it out! Hooray!
Haha... the "add conditional column" and the "filter down" where the cool actions!
Thx to your help! Couldn't have pulled it off without you.
I'd proudly would like to show you my version but since the thread is closed I can't upload a file anymore aparently...

Remains one issue though...
How do I get new data into the query?
Tomorrow I want to generate a new query without going through the same steps... how do I pull that of? (allthough it will be a good exercise to repeat the steps a couple of times).

Already tried to delete the output and alter the input table... and run the query... no good.
So what's the trick here?

grts Jo