Data Source Setting

[Rafi35]

Hello all, when i changed my data source it showed below message. what is the reason for that? Both sources are excel files and header is same. i was just switching source. what is the correct way to change data source?

[Anonymous]

Hi @Rafi35 ,

Has your problem been solved? If not, please share more details so we can help you better.

Best regards,
Community Support Team_ Scott Chang

[AnkitKukreja]

Hi! @Rafi35 

As explained by Sam it could be due to one of the reasons, I would generally click on all the applied steps on right and identify on which step it is failing? On the step where issue can be identified you would be able to identify the cause.

[SamInogic]

Hi,

The error message in the screenshot, "The key didn't match any rows in the table", indicates that Power BI cannot find the expected data in the table based on a specific key or column that your report or query is looking for. This error commonly occurs when:

1. Missing Key Columns: The new Excel file you switched to might not contain a column that Power BI expects to act as a key (e.g., an ID, submission number, or other unique identifier).
2. Data Type Mismatch: The key column in the new file may have different data types compared to the original (e.g., numbers vs. text), preventing Power BI from matching the rows.
3. Empty or Blank Values: If the key column has blank or null values in the new file, Power BI won’t be able to match rows based on these empty keys.
4. Sheet/Table Naming Issues: Power BI may not be pointing to the correct sheet or table in the new Excel file if the structure differs slightly.

How to Fix It:

1. Check the Key Column:

• Open the new Excel file and ensure the key column (e.g., "submission no" or any column used as an identifier in your report) exists and has valid values in all rows.
• Check if there are any blank or null values in this column. If so, you may need to fill in missing data or filter out those rows.

2. Verify the Data Type:

• Ensure that the data type of the key column in the new file matches the expected data type in Power BI. For example, if Power BI expects numbers and the new file contains text, it will throw this error.
• In Power Query Editor, you can check and change the data type of the key column by selecting the column and adjusting the type in the top toolbar.

3. Check the Sheet/Table Name:

• Confirm that Power BI is correctly referencing the new sheet or table from the Excel file. Go to Power Query Editor and ensure that the correct table or sheet is being selected.

4. Review Power Query Steps:

• Go to Transform Data > Transform Data (to open Power Query Editor).
• Inspect each step on the right panel (under Applied Steps) and ensure all transformations or filtering steps are compatible with the new file.
• Pay particular attention to any filtering, merging, or transformation steps that might be using the key column. If the column is missing or different, you may need to update those steps.

5. Refresh and Apply Changes:

• After making the necessary changes, click on Close & Apply in Power Query Editor to apply the updates and refresh the report.

Hope this helps.

Thanks!