Automatically Gather Extra Data

[Mat42]

I've searched for an answer, but haven't yet found anything useful. If this is a duplicate question, let me know.

I've been tasked with producing a report from a Sharepoint folder of identically formatted sales manifests. The information contained within the documents will obviously be different, but the document layout is identical. This means that the transformation process for each document will also be identical. However, the number of documents within the folder will increase as time goes by, so this will mean adding these new documents to the report.

I'm aware of the process of copying the code for the transformation steps in a query and pasting them into a new query, but is there a way to automate that process?

As an example: imagine I have 4 documents in the folder and I set up identical data transformations to get them into a format that PBI can utilise. I then append the resulting tables together to form a master table. At present, if 2 more documents are added to the folder I have to manually add those documents to the report by using the above mentioned 'copy/paste' of the transformation steps.

Can I automate this? Can I create a set of transformation steps, pull all documents from a folder, then apply those steps to every document?

It's the sort of thing that I can automate easily within Excel using VBA, but am not familiar enough with PBI to known where I'd start.

[parry2k]

@Mat42 you add a new column, pass content to your transformation function, and that your function will return a table and then you expand it. check this another post

Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[parry2k · Accepted solution]

@Mat42 yes, you don't need to manually add new documents in PQ, you just need to reach the folder and process all the files. Check this awesome post.

Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Mat42]

Thanks for your help and really quick reply 😊.

So, I connect to the folder and can list of documents within the folder. How do I go about performing the same transformation steps on every document? I can see the combine option, but the files need a lot of transformation before they're combined. 

Sorry if that's an obvious question.

[Mat42]

Ignore the above. I got it all sorted, thanks. I spent 2 days trying to working out why it wasn't working and it turns out that the data I was pulling through was dated incorrectly so at first (and second, third, fourth etc) glance, it looked like the same data being pulled through multiple times when, in actuality, it was the correct data just with the wrong date on it.

And, apologies for the delay in replying.