Power BI Insights: Infer sheet dimensions; DirectQuery; Fuzzy matches

Power BI bloggers share tips on loading problems with Power Query, creating DirectQuery models and fuzzy matches.

Solving loading problems with Power Query

Chris Webb shared his thoughts on a new Power Query feature released in December for Power BI Desktop. The InferSheetDimensions Excel.Workbook function can infer the value for certain cells from the worksheet itself in situations where the dimensions metadata is incorrect. Currently, this is only supported for Open XML files and not legacy Excel files.

On its docs page, Microsoft provides a much more detailed description of the new option with a section on how to troubleshoot issues where Power Query isn't loading all the data from the worksheet. He wrote:

Open XML format Excel workbooks (ie xlsx and xlsm format files) have some metadata (the “dimensions metadata” referred to in the docs) in them that acts as a kind of bounding box describing the range of cells in each worksheet that contain values. By default, Power Query uses this metadata when it gets data from an Excel worksheet – although not when it gets data from an Excel table or named range. Most of the time this metadata is reliable but in some cases, for example where an xlsx file has been generated by third-party software that has “export to Excel” functionality, it contains inaccurate information. This doesn’t affect Excel, which still loads the workbook, but it means that Power Query may not load all the data present in a worksheet. In these cases, setting InferSheetDimensions=true in Excel.Workbook causes Power Query to ignore the dimensions metadata and load all the data from the worksheet.

Using a Power BI dataset and local Excel file to create a DirectQuery model

FREE Membership Required to View Full Content:

Joining MSDynamicsWorld.com gives you free, unlimited access to news, analysis, white papers, case studies, product brochures, and more. You can also receive periodic email newsletters with the latest relevant articles and content updates.
Learn more about us here

Get full access

or login