Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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?
Solved! Go to Solution.
@rodneyc8063_1 , if they have nothing in common with each other, unfortunately you'll have to transform them all separately. =(
@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
As you can see, in this sample, I've asked for 4 columns but since only 2 exists, only 2 is returned.
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.
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. =(
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! 🙂
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
103 | |
98 | |
90 | |
70 |
User | Count |
---|---|
166 | |
131 | |
129 | |
102 | |
98 |