Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have 78 .csv files in a folder.
They all contain a subset of the same 13 columns. Some files contain only 4 columns while one file contains all 13 columns.
The columns are not layed out consistently. E..g. File 1 has [Weight] in Column A while file 2 has [Date] in Column A.
There are 34 unique formats.
What is the most efficient way to merge the 78 files into one dataset?
I could set up 34 folders (for the 34 unique formats) but hoping there is an easier way.
Solved! Go to Solution.
If the column names are all the same (if they are present), you can add a "select columns" in the query that transforms the sample file.
Syntax:
Table.SelectColumns(table as table, columns as any, optional missingField as MissingField.Type) as table
As missingField use MissingField.UseNull
This will insert the column with null values.
If the column names are not the same, you may need to apply renames first, using Table.RenameColumns, with syntax:
Table.RenameColumns(table as table, renames as list, optional missingField as MissingField.Type) as table
In this case, you can use MissingField.Ignore
If the column names are all the same (if they are present), you can add a "select columns" in the query that transforms the sample file.
Syntax:
Table.SelectColumns(table as table, columns as any, optional missingField as MissingField.Type) as table
As missingField use MissingField.UseNull
This will insert the column with null values.
If the column names are not the same, you may need to apply renames first, using Table.RenameColumns, with syntax:
Table.RenameColumns(table as table, renames as list, optional missingField as MissingField.Type) as table
In this case, you can use MissingField.Ignore
@MarcelBeug Thanks for your help. The columns have the same name so your "Select columns" suggestions worked nicely!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
105 | |
69 | |
67 | |
43 |
User | Count |
---|---|
148 | |
103 | |
103 | |
88 | |
66 |