Understanding the elements of 'Source' in Power Query

[thomasfmeier]

I use the data source 'Hashtag items' in a query. This report gets refreshed monthly and I got an error in the most recent execution I got this erros

When checking the source step in the query I got this picture

Then I compared 'Source' with an earlier month and found 3 additional columns. Using this file the error disappeared

There's no change to the source system or report so firstly what do these details mean and why would this be different? Can I update the actual query - = Excel.Workbook(File.Contents(Filepath & "Hashtag Items.xlsx"), null, true) - in any way to address this?

FYI, 'Filepath' is determined in the step before; i.e.  Filepath = Excel.CurrentWorkbook(){[Name="Filepath"]}[Content]{0}[Column1],

this allows me to place the source files into any folder without hardcoding it.

[SacheeTh · Accepted solution]

Hi @thomasfmeier ,

The error you encountered and its resolution likely relate to how Power Query handles schema changes in your data source, particularly the addition of new columns. Let's break down the elements and address your questions:

Here's an example of how to make the Source step more flexible:

let
    Filepath = Excel.CurrentWorkbook(){[Name="Filepath"]}[Content]{0}[Column1],
    Source = Excel.Workbook(File.Contents(Filepath & "Hashtag Items.xlsx"), null, true),
    ColumnsToKeep = {"Column1", "Column2", "Column3"},  // Adjust based on your actual column names
    FilteredTable = Table.SelectColumns(Source, ColumnsToKeep)
in
    FilteredTable

This approach ensures that your query works even if additional columns are added to the file. 

 

 

 

I'll try to explain the method I used here in simple way with some examples

1. Error Due to Schema Mismatch: Power Query expects the source data's schema (e.g., column names and their order) to remain consistent. If columns are added, removed, or renamed in the source file, it may cause errors.

2. Resolution by Updating the File: Using a file with the additional columns resolved the issue because it matched the schema Power Query expected at that step.

---

Elements of the Query

• Source Step:

  = Excel.Workbook(File.Contents(Filepath & "Hashtag Items.xlsx"), null, true)

  • This reads the Excel file specified by Filepath and retrieves its contents as a workbook object.

• Filepath Step:

  Filepath = Excel.CurrentWorkbook(){[Name="Filepath"]}[Content]{0}[Column1]

  • Dynamically determines the file path, allowing flexibility in where you store the source files.

---

Why Is This Different?

1. Power Query determines the schema of the file at the time the query is created or refreshed. If the file's structure changes (e.g., new columns are added), it may not align with the query's expectations, causing errors.

2.  While the source system may not have changed, the version of the file used for the query might include updates (e.g., additional columns). This mismatch can cause errors.

---

Solutions to Handle Schema Changes

To make your query more robust against schema changes, you can update it as follows:

1. Dynamically Detect Columns:

   • Modify your query to handle dynamic column detection:

     = Table.SelectColumns(Source, List.FirstN(Table.ColumnNames(Source), X))

     Replace X with the number of columns you want to process, or adapt based on column names.

2. Ignore Extra Columns:

   • Use the Table.RemoveColumns function to remove unexpected columns:

     = Table.RemoveColumns(Source, List.Difference(Table.ColumnNames(Source), ExpectedColumns))

     Replace ExpectedColumns with a list of column names you want to retain.

3. Adjust Based on New Schema:

   • If you want Power Query to accommodate the new schema automatically, modify the steps referencing the columns. Use the "Column Index" instead of column names when possible.

---

 

[v-saisrao-msft]

Hi @thomasfmeier 
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

[v-saisrao-msft]

Hi @thomasfmeier,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-saisrao-msft]

Hi @thomasfmeier 
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[SacheeTh · Accepted solution]

Hi @thomasfmeier ,

The error you encountered and its resolution likely relate to how Power Query handles schema changes in your data source, particularly the addition of new columns. Let's break down the elements and address your questions:

Here's an example of how to make the Source step more flexible:

let
    Filepath = Excel.CurrentWorkbook(){[Name="Filepath"]}[Content]{0}[Column1],
    Source = Excel.Workbook(File.Contents(Filepath & "Hashtag Items.xlsx"), null, true),
    ColumnsToKeep = {"Column1", "Column2", "Column3"},  // Adjust based on your actual column names
    FilteredTable = Table.SelectColumns(Source, ColumnsToKeep)
in
    FilteredTable

This approach ensures that your query works even if additional columns are added to the file. 

 

 

 

I'll try to explain the method I used here in simple way with some examples

1. Error Due to Schema Mismatch: Power Query expects the source data's schema (e.g., column names and their order) to remain consistent. If columns are added, removed, or renamed in the source file, it may cause errors.

2. Resolution by Updating the File: Using a file with the additional columns resolved the issue because it matched the schema Power Query expected at that step.

---

Elements of the Query

• Source Step:

  = Excel.Workbook(File.Contents(Filepath & "Hashtag Items.xlsx"), null, true)

  • This reads the Excel file specified by Filepath and retrieves its contents as a workbook object.

• Filepath Step:

  Filepath = Excel.CurrentWorkbook(){[Name="Filepath"]}[Content]{0}[Column1]

  • Dynamically determines the file path, allowing flexibility in where you store the source files.

---

Why Is This Different?

1. Power Query determines the schema of the file at the time the query is created or refreshed. If the file's structure changes (e.g., new columns are added), it may not align with the query's expectations, causing errors.

2.  While the source system may not have changed, the version of the file used for the query might include updates (e.g., additional columns). This mismatch can cause errors.

---

Solutions to Handle Schema Changes

To make your query more robust against schema changes, you can update it as follows:

1. Dynamically Detect Columns:

   • Modify your query to handle dynamic column detection:

     = Table.SelectColumns(Source, List.FirstN(Table.ColumnNames(Source), X))

     Replace X with the number of columns you want to process, or adapt based on column names.

2. Ignore Extra Columns:

   • Use the Table.RemoveColumns function to remove unexpected columns:

     = Table.RemoveColumns(Source, List.Difference(Table.ColumnNames(Source), ExpectedColumns))

     Replace ExpectedColumns with a list of column names you want to retain.

3. Adjust Based on New Schema:

   • If you want Power Query to accommodate the new schema automatically, modify the steps referencing the columns. Use the "Column Index" instead of column names when possible.

---

 

[thomasfmeier]

Thanks for the response.

If I understand this correctly you refer to changes to the file layout; e.g. addition/removal of columns, right?

Does your response apply to the following 2 conditions:

1. If I have the same number of columns and just different data (the file is refreshed monthly)
2. The file contains the same number of columns but the column name might have changed

[v-saisrao-msft · Accepted solution]

Hi @thomasfmeier 

Thanks for reaching out to the Microsoft forum community. Sorry for the delay in response, based on the concerns that you're facing issues when refreshing the report, especially related to schema changes in the source file. 

1. As long as the number of columns remains unchanged and only the data within those columns is updated (as expected during a monthly refresh), your query should function without any issues. Problems occur when the structure of the file is altered. 
2. If column names are changed, Power Query will attempt to match the query to the old names and will generate an error if the columns no longer exist with their expected names. To address this, you can use a more dynamic approach to accommodate renamed columns. 

• If the file’s structure changes often, it’s worth updating the query to dynamically detect columns to handle future schema changes. 

If this post helps, please give us Kudos and consider Accept it as a solution to help the other members find it more quickly. 

Thank you.