Excel Power Query

power-query

#1

Hi - Is anyone using Power Query to manipulate the raw data coming out of DataDear to produce a cleaner data set for client reporting?

I am trying to combine the invoice and credit note information into a single data set (with enrichment from lookup tables) but because I am doing this across multiple companies I was hoping to copy the m-code over for each instance, but this isn’t working.

I am not an m-code expert, so it might be a problem with my coding, but I can’t work out why the most simple code won’t come across.

Any help would be appreciated.
Thanks
Richard


#2

@lancerubin maybe can help with this?


#3

Hi - I managed to figure this out last night. There are spaces in the field names which seems to cause problems when copying the code across. It even causes problems when copying from one step to another.

I now remove the spaces manually as a first step and then am able to copy the rest of the code after that and it works just fine.

Thanks for following up though Neville.

@lancerubin it might still be worth connecting to see how you are using Power Query and DataDear?


#4

Hi Richard - thank you for sharing this info!

Happy DataDearing!


#5

Hi Richard, we dont use Power Query generally to get data from accounting packages.

We really use either Modano to pull into a 3-way rolling model at chart of account level into the PL and BS of a model, or we use DataDear for the purpose of pulling transaction level information and also tracking codes.

Modano is deficient in some areas which is why we opted to add DataDear into our tool kit as it has a lot more functionality in terms of transaction level and tracking code level data. It also enables the pushing of information back into the cloud which is extremely powerful for automating finance tassks like journal postings etc.

Happy to have a chat.

We can do a hangout/skype/whatsapp call, just book something using this link.

www.calendly.com/model_citizn

Chat soon and happy DataDearing!


#6

Hi Richard, this is Richard.
I think I’m trying to do something very similar to you and having some frustrations.
One big frustration I have is that the amount of data we have on Xero is so vast that we can only download the data in blocks - month by month is currently my choice.
Next frustration is that the Field Names for the Credit Notes are different from the Invoices, even though the data is fundamentally the same. If the field names were the same then it would be easier to merge the data from the two tables. An alternative to this would be if Datadear allowed you to change the column headers but I don’t think it does.
But my main one is what you alluded to of needing to copy the code from one worksheet or workbook to another. I am a little new to this and don’t know what ‘m-code’ is but I assume we’re talking about something similar.
What I thought I ought to be able to do was to get one worksheet working to my liking and then to be able to copy that worksheet and change the Datadear parameters and/or reporting criteria.But so far it doesn’t seem as simple as using the Move or Copy Sheet tool.
Does anyone have a simple way to do this?