Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
rodneyc8063_1
Helper V
Helper V

Load data from folder multiple CSV files different schema (Power Query) - Possible?

I was recently given a data dump of roughly 50 CSV files - Each CSV file represents a table of data, each being a different table/column/schema structure.

 

I know I can "combine and load" if all the CSV files were the same and located in one folder

 

I was wondering in this case, if each CSV is a different schema - How can I load all of these?

 

Do I actually have to go to each CSV file individually and load it?

1 ACCEPTED SOLUTION

@rodneyc8063_1 , if they have nothing in common with each other, unfortunately you'll have to transform them all separately. =(



Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!

View solution in original post

4 REPLIES 4
hnguy71
Memorable Member
Memorable Member

@rodneyc8063_1 , typically I would combine files that are like for like within the same folder. You could possibly explore the data and group them by specific folders.

 

However, if you know that in each of the CSV files you're picking up only the required fields, it's very possible to just have them dumped into one folder with the following transformation logic within the helper query:

 

1. Select all files in folder

2. Promote header. Remove fixed column evaluation at your source step

3. Select only required columns. Add optional MissingField.Ignore

 

let
    // Remove fixed column criteria
    Source = Csv.Document(Parameter1,[Delimiter=",", Encoding=1252, QuoteStyle=QuoteStyle.None]),
    // Go ahead and promote all headers
    PromoteHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    // Select columns. Ensure you add the optional MissingField.Ignore
    SelectRequiredColumns = Table.SelectColumns(PromoteHeaders,{"Column1", "Column2", "Column3", "Column4"}, MissingField.Ignore)
in
    SelectRequiredColumns

 

hnguy71_2-1651287280932.png

 

 

As you can see, in this sample, I've asked for 4 columns but since only 2 exists, only 2 is returned.

hnguy71_0-1651287049432.png

 

And when I expand to combine all the CSV files, in one file I have two additional columns and it'll pick it up as well.

hnguy71_1-1651287236724.png

 



Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!

Hi @hnguy71 

 

This is definitely good to know and a neat trick!

 

Hm hopefully I didnt misunderstand your trick, but it sounds like "combining files within a folder" usually is for files that are like for like?

 

In my case for example I have a CSV file of name, then another CSV file of address, then another of telephone number etc. So each CSV file is a table in itself with unrelated columns.

 

I was hoping that there would be something where I could just select all the files and then let Power Query grab everything and load it all vs having to open each file and load it individually.

 

Not sure if something like this exists? 

@rodneyc8063_1 , if they have nothing in common with each other, unfortunately you'll have to transform them all separately. =(



Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!

Thanks for confirming this sad piece of news 😞

 

Also thank you for the neat trick, I will be sure to keep it in mind if I do need to do a mass import of similar files! 🙂

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.