Column header name changed in data source - get data from folder

Power Query
1

Dear All!
I would like kindly ask for your support in the following case.

I am importing excel tables from folder.
Those tables have 4 columns. A few days ago 2 column headers name got changed. Now the report refresh breaks.

Do you have any suggestion, how can I deal with column name changes when importing data from folder?

Thank you very much for your help
Best regards,
S

2

Hi @Alexander,

Welcome to the forum!

See if this is helpful.

1 Like
3

Dear Melissa!
Unfortunately I cannot get it working.

I made 2 test files.

  1. Header names: Date; Revenue1; Cost1
  2. Header names : Date; Revenue2; Cost1

Steps:

  • import from folder: test file 1
  • as per the video (2nd solution):
  • remove changed type from applied steps
  • as I imported data from folder the last applied step was Expanded Table Column1 (in the video it was #Promoted headers, because the BI was linked to a single excel file.)

The M code is the following:

#“Expanded Table Column1” = Table.ExpandTableColumn(#“Removed Other Columns1”, “Transform File”, Table.ColumnNames(#“Transform File”(#“Sample File”))),
Custom1 = Table.ColumnNames(#“Expanded Table Column1”){2}, // First column is the source name.
Custom2 = #“Expanded Table Column1”,
#“Renamed Columns” = Table.RenameColumns(Custom2,{{Table.ColumnNames(#“Expanded Table Column1”){2}, “Revenue”}}),
#“Changed Type” = Table.TransformColumnTypes(#“Renamed Columns”,{{“Source.Name”, type text}, {“Date”, type date}, {“Revenue”, Int64.Type}, {“Cost1”, Int64.Type}})
in
#“Changed Type”

After pulling the test file 2 into the source folder I got null values In the Revenue column from the test file 2.
I cannot see what is going wrong. The only difference from the video’s sample as I am importing data from a folder and on from a single file.

Looking forward to hearing from you, or anyone else from the forum.
Thank you in advance,
BR,
S

4

HI @Alexander,

Quick question for you.
Do potentionally only the column names change and is the structure (so column order AND number of columns) the same for each file placed in the folder?

Thanks!

5

1.xlsx (10.2 KB) 2.xlsx (10.2 KB) PQ issue.pbix (39.0 KB)

Dear Melissa!

The structure is identical. See the files attached.
Br,
S

6

Hi @Alexander,

I’ve added a FolderPath parameter, select your folder path from the list to restore the queries.

image
.

Duplicated your Transform Sample File query
Retrieved and Transformed the Column Names

image
image975×290 22.5 KB

.

In your Source query added a step to Rename the Columns

image
image971×522 30.6 KB

.

With this result.

image
image863×246 11.1 KB

.

Here’s your sample file. PQ issue.pbix (43.1 KB)
I hope this is helpful.

7

Dear Melissa!

Yes, this can be a solution. Thank you very much.

However I am still wondering, why your first suggestion (posted video) was not working.
I have found that particular solution in several videos (Curbal exmpl.). Everyone was importing data from a single excel file only. I guess, for an exact reason.

Do you know why the 2 types of importing is making such a difference with this solution?
Thank you
S.

p.s.: In the meanwhile I found another solution for this particular issue. In case of importing from folder.

  1. You have to plan ahead and be prepared for column header name change
  2. Uncheck the box: Options: Data load: Detect column types and header for unstructured sources"
  3. Import data from folder
  4. You can name the headers by yourself in PQ.
  5. Chose the correct data type
  6. In some columns, for example where the data type is INT the original header names will cause an error.
  7. Excluding error

Whit this solution the column header name can change every time, it will not cause a refresh error.
The extra job to be done is to get prepared for column header name changes. Be proactive and not reactive.