Best way to structure, save and name data sources

[LT4RFF]

Good morning,

This is more of a recommended best practice question.

The idea being that when my data library builds up over time, that it is in the best position to be pumped into Power BI.

I receive monthly accounts from different vendors.

I would like to take those monthly accounts from different vendors and append them into one spreadhseet so that I may slice & dice the data as a whole as and when I want.

At the moment I have multiple folders for each vendor with each monthly account labelled 2024.01, 2024.02, 2024.03 etc etc

So for example:

Vendor 1/ 2024.01, 2024.02, 2024.03, 2024.04

Vendor 2/ 2024.01, 2024.02, 2024.03, 2024.04

Vendor 3/ 2024.01, 2024.02, 2024.03, 2024.04

As each month comes in I just add the account to the folder and rename it.

Here my question: Is that the best way of doing it?

Or would it be better to have 1 big folder and rename the files 2024.01 Vendor 1, 2024.01 Vendor 2, 2024.03 Vendor 3 etc etc

Basically I want to be in a position where all my various data sources get saved in a centalised location and I can just hit refresh on the dashboard with minimal problems.

I want to establish that now before I find myself a year down the line and all my data has been saved inefficiently.

[Nonessential]

I would start with youtube - how to import files into power bi, and then search for 'How to transform data in power query' and then 'How to append data in Power Query'. There is an abundance of training material out there. If you get stuck with something in particular come back to the Forum and someone will be happy to help.

[Nonessential]

Hi, assuming the files are in the same format you could save them as .CSV in the same folder location and use Power Query to append all of the files into a single table using the folder as the data source. https://learn.microsoft.com/en-us/power-query/combine-files-csv

[LT4RFF]

When you say 'same format' I assume you mean all the columns match up?

They don't.

Each vendor uses the same columns but they are in different places depending on the vendor.

So 'Customer Ref' may be column A for Vendor 1 but column D for Vendor 2.

I will need to append all the different Vendors and somehow line up the correct columns....that's for another day 🙈

[Nonessential]

Ah...absolute nightmare! Could you create a template for your Vendors to use which has the same formatting and ask them nicely to use in place of their own? Otherwise you may need have to have several different folder locations for each different format, automate the changes needed to align each file in power query and then append them all as new into a new table.....

[LT4RFF]

Unfortunately not...

'Otherwise you may need have to have several different folder locations for each different format, automate the changes needed to align each file in power query and then append them all as new into a new table......'

I think this is what I currently have and what I am about to attempt...

I think that answers the question - keep the folder structure as it is.

and that now leads into 'format, automate the changes needed to align each file in power query and then append them all as new into a new table......'

Could you point me in the right direction to learn how to do that please?

[Anonymous · Accepted solution]

Thanks for the reply from Nonessential , please allow me to provide another insight:

Hi, @LT4RFF 

Regarding the issue you raised, my solution is as follows:
 

Could you please let us know if Nonessential's response resolved your issue? If it did, kindly accept it as the solution.

Of course, if you have your own solution, feel free to share it. This will help other community members with similar issues find a resolution more quickly.

It certainly seems that you are facing quite a significant issue based on your question.

I have a suggestion: if part of the file data structures are consistent, you might consider merging these into one.
 

This way, completing the task in sections could be more efficient.

For further details, please refer to:
Power Query Folder connector - Power Query | Microsoft Learn

Combine files overview - Power Query | Microsoft Learn
 

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.