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?


#7

Hi Richard, I would be happy to discuss how you can use a range of tools to do this. DataDear is certainly great at getting both lower level and higher level information which you can then slice and dice all you like.

Its often difficult to see just over messages.

Depending on your location/time zone it might even be easier for the other experts to help.

We are based in Melbourne Australia.


#8

Hi Lance - I’m in China for the next 5 days or so; so we’re on a fairly similar time zone I think.
Early in the morning is usually good for me. From about 9am onwards. It’s currently 1.07pm here.
Let me know how we could make contact.


#9

Hi Richard, sorry just connected Concourse so I get more notifications.

You can either book some time using this link or I should have time on Friday late morning ie before midday.

Perhaps send an email at lance@modelcitizn.com and my assistant can try connect us when suitable.

Thanks
Lance


#10

Link


#11

Hi Richard,

I just connected PowerBI to the DD SmartPL reporting template comnected to our own data, as it was awesome.

I will be releasing the PBI file soon to share. Safe travels.