Monthly Archives: October 2019

Business Intelligence

Locale parameter in power query

Please note, that this issue took place in power query editor, not in Excel application side.

Product and price rows are of text type. Price should be numeric field, that calculations could be carried out with price row.

After changing the row to decimal number, the row shows an error.

This issue can be tackled with query options.

Select file – options and settings – query options.

Take regional settings – locale.

As the decimal separator was a dot, the local settings should be changed to English.

Now the data type can be changed.

Excel

Can’t find project or library

This post is related to my earlier post “Excel cannot save”. If you have the error “Can’t find project or library”, it is useful to check updated both for Windows and for Office.

However, the error above “Can’t find project or library”, might still appear even though everything is updated. The macros for an Excel workbook have disappeared. Excel gets stuck and needs rebooting.

I have seen error like this for Excel template documents with macros, which are kept open long times like throughout the work day. For example, you download data from system A, paste the data to Excel template, run macros in Excel template and/or execute needed manual steps, copy the data to clipboard, paste the data to system B. Template like that can stay constantly open and you don’t save different version of Excel daily.

The root cause, which causes the error, is still unclear for me. However, something happens on the background and Excel workbooks with macros, which are kept open, loose macros for some reason.

To have a workaround, save a backup for the file and create documentation for your macros. It is easy to have a backup of template Excel. When template is developed, update also backup. If something happens with template, take the backup to use. The other option is to document the macros. Then the macros can be replicated based on the documentation.