For any raw data that is consumed in power query, certain things need to be ensured. A few such items have been listed below.
Power query is case sensitive #
Example : Columns Structure
Data Set 1: Sr No, Particulars, Product, Debit, Credit
Data Set 2: Sr No, Particulars, Product, debit, credit
Since Power query is case sensitive, it will treat “Debit” & “Credit” columns in Data Set 1 and “debit” & “credit” columns in Data Set 2 differently.
Columns Structure should be same #
Power query has the ability to generate few steps on its own when you import the data. Steps like Promote headers and Change Type are generated automatically on data import in Power query. In case it is not same, it will not generate the report.
Suppose you generate report using power query each month. The column structure for the 2 months are:
April,2019: Sr No, Particulars, Product, Debit, Credit
May,2019: Particulars, Product, debit, credit As we can see, “Sr No” is missing from May, 2019 data. Thus, the step “Promote headers” and “Change Type” will generate error and report generation process will be interrupted.
Sheet Name should be same #
When we import data into Power query, “Sheet Name” should be same as it was while developing the report otherwise it will not be able to proceed with the report generation process.
Suppose, we were processing sales data of an organisation split into multiple files belonging to each month. Now while building the power query report, we had used sales data of April, 2019 saved in “Sheet1” of the file. In case sales data for the month of May, 2019 is saved in “Sheet2” of the file, it will interrupt the report generation process.
File type extension #
Even though Power query supports multiple data formats, it is preferable to keep your data in “.xlsx” format in case the data is pulled from excel files.
Note: In case data is kept in “.xls” format, Power query imports masked values/ formatted cell values whereas in “.xlsx” the value of each cell is taken into consideration without considering the cell format.
