Hello all
I am using Power Query in Excel 365. I connected using Files From Folder to import 10 csv files, each approx 3000 rows, 20 columns.
In my Transform Sample File step, only very basic transformations were made to create the custom function transformation.
To get around the fact that they have different headers, I drilled down into the column with the tables and used the following to extract a dynamic list of headers, which I then referenced when I expanded: (Thanks @wynhopkins !)
List.Union (List.Transform( PreExpand[Transform File], each Table.ColumnNames(_)) )
Everytime I add a step after expanding it takes around 10 minutes to apply it - below is a screenshot of the bottom right of my screen showing it cycling through each individual file. Each step is just basic from the UI like change type or replace value.
I tried adding Table.Buffer as the first step after expanding but it made no difference. I also tried keeping just the top 100 rows but this also had no impact.
Any idea what might be causing this and how I can fix it please?
Hi @Holly454
Sorry for the late reply.
I see, you've used the wizard to combine the files.
Can you please try the old-fashioned way below:
let
Source = Folder.Files(REDACTED),
#"Removed Other Columns" = Table.SelectColumns(Source,{"Content", "Name"}),
#"Added Custom" = Table.AddColumn(#"Removed Other Columns", "CsvDocument", each Csv.Document([Content])),
// try to standardize column names like this
#"Added CsvDocumentStandardColumns" = Table.AddColumn(#"Added Custom", "CsvDocumentStandardColumns", each Table.RenameColumns([CsvDocument],{
// add below all renames needed for all files
{"Columnx", "AnotherColumnName"},
{"ColumnY", "Something"}
// ...
// below will make sure you get no error case a rename does not apply
}, MissingField.Ignore))
in
#"Added CsvDocumentStandardColumns"
And then Expand...
...and then add all your subsequent steps.
IF/when you notice performance problems, please paste here your new query based on my initial code.
I edited my answer as I saw you have different column names... let's hope this works.
Please mark this as answer if it helped.
Hi @Holly454
Sorry for the late reply.
I see, you've used the wizard to combine the files.
Can you please try the old-fashioned way below:
let
Source = Folder.Files(REDACTED),
#"Removed Other Columns" = Table.SelectColumns(Source,{"Content", "Name"}),
#"Added Custom" = Table.AddColumn(#"Removed Other Columns", "CsvDocument", each Csv.Document([Content])),
// try to standardize column names like this
#"Added CsvDocumentStandardColumns" = Table.AddColumn(#"Added Custom", "CsvDocumentStandardColumns", each Table.RenameColumns([CsvDocument],{
// add below all renames needed for all files
{"Columnx", "AnotherColumnName"},
{"ColumnY", "Something"}
// ...
// below will make sure you get no error case a rename does not apply
}, MissingField.Ignore))
in
#"Added CsvDocumentStandardColumns"
And then Expand...
...and then add all your subsequent steps.
IF/when you notice performance problems, please paste here your new query based on my initial code.
I edited my answer as I saw you have different column names... let's hope this works.
Please mark this as answer if it helped.
Helpful resources
Microsoft Fabric Learn Together
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Power BI Monthly Update - April 2024
Check out the April 2024 Power BI update to learn about new features.
Fabric Community Update - April 2024
Find out what's new and trending in the Fabric Community.
