Workbooks and Supporting Documents

For Master Your Data with Excel and Power BI

(Second Edition of M is for Data Monkey)

You probably got here from the link in our book, Master Your Data with Excel and Power BI. Click the button below to download all the example files that were used in the book.

More information on connecting to the sample Azure database referred to in Chapter 12 – Relational Data Sources, as well as troubleshooting techniques, are detailed further down this page.

Notes/Disclaimers

The Content is provided “as is”, without warranty of any kind, either express or implied. The Content contains valuable, proprietary content owned by the The Publisher and/or The Authors that is protected by state, federal, and international law, including, without limitation, copyright and trade secret laws. You may use the Content strictly for your own individual, personal use. The Content shall not be distributed, re-sold or used in a commercial manner

Connecting to our Azure Database

Database Credentials

In order to connect to the database used in Master Your Data for Excel and Power BI, please use the following connection information:

Create a new query –> From Azure –> From Microsoft Azure SQL Database
Connect to the following database:
- Server: xlgdemos.database.windows.net
- Database: AdventureWorks

When prompted for security information, make sure you select/enter:

Database (not Windows or Microsoft Account)
Username: DataMaster
Password: D4t4M@ster!

And at that point you should be good to go!

Still Can’t Connect?

Our server is running 24/7/365, so the most likely issue is that you are behind a firewall that is blocking your access. Try connecting from another location or via a VPN to see if this is the case.

Master Your Data Errata

We do actively track issues within Master Your Data for Excel and Power BI. A listing of all errata items (by chapter) can be found below.

Chapter 1

Page 17

3rd sentence of the final paragraph should read:
- We’re not
  going to do this now, but if you wanted to, you would go to the Applied Steps window and click the X to the
  left of the ~~Renamed~~ Removed Columns step.
Fixed in Digital Edition: 2023-01
Fixed in Print Edition: 2nd Printing

Page 19

List item 3 at the bottom of the page should read:
- While performing two “like” actions in succession results in the steps being combined (as we saw
  with the ~~Removed~~ Renamed Columns step), similar actions will not be combined into a single step if there is a
  different step between them
Fixed in Digital Edition: 2023-01
Fixed in Print Edition: 2nd Printing

Chapter 4

Page 72

In the text we indicate that you can change the path to an imported file via the Home -> Transform Data -> Data Source Settings dialog. Unfortunately there appears to be a bug in Power BI Desktop that is causing the Change Source button to be greyed out (and unclickable). Until this bug is fixed, the only way to update a connection is to edit the Source step via the Formula bar or edit the M code directly.
Fixed in Digital Edition: 2023-01
Fixed in Print Edition: 2nd Printing

Chapter 5

Page 85

2nd to last bullet should read as follows:
- Select the Tran Date column -> Home tab -> Remove Rows -> Remove Errors
Fixed in Digital Edition: 2023-01
Fixed in Print Edition: 2nd Printing

Chapter 9

Page 147

The final warning should read:
- What happens if you fail to anticipate an issue and get a step level error in one of the files when you run the Combine? You will need to debug it, of course! Go back to the FilesList query and insert temporary steps to keep the top x rows or remove top x rows until you discover which file ~~query~~ causes the error. Once you have it as the first file ~~query~~ in the FilesList query, you can then debug it in the Transform Sample File query to see what went wrong.

Fixed in Digital Edition: 2023-01
Fixed in Print Edition: 2nd Printing

Chapter 13

Page 220

The 3rd bullet should read:
- Right-click the ~~Attribute~~ Value column -> replace values -> replace | with null
Fixed in Digital Edition: TBA
Fixed in Print Edition: TBA

Chapter 14

Page 233

Final bullet should read as follows:
- “Pressing escape will dismiss the Intellisense, while typing ~~a space,~~ tab, or enter will accept the Intellisense suggestion”
Fixed in Digital Edition: 2023-01
Fixed in Print Edition: 2nd Printing

Page 238

Final bullet should read as follows:
- Name the column “Pays Options Dues” and use the following formula:
Fixed in Digital Edition: 2021-11-30
Fixed in Print Edition: 2nd Printing

Chapter 15

Page 247

The last bullet should read:
- Structured – which ~~appears~~ appear in a colored font ~~font~~ such as table, list, record and even function. They ~~are~~ may be constructed from primitive values or other structured values.
Fixed in Digital Edition: TBA
Fixed in Print Edition: TBA

Chapter 19

Page 348

The Parts bullet should read:
- Parts – dynamically references the correct list of binary files (based on the value of the Use Buffering parameter) extracting on the Forecast Parts data from each file.
Fixed in Digital Edition: 2021-11-30
Fixed in Print Edition: 2nd Printing

Submitting Issues for Errata Inclusion

If you notice a possible error in the book, please submit the issue via our online form.

Skillwave is brought to you by:

This site is produced and delivered to you by Excelerator BI Pty Ltd. and Excelguru Consulting Inc.