Tag Archives: Data Explorer

A frown, or two – Data Explorer to PowerPivot gotchas.

As I continue my discovery of all things Data Explorer and M I’m not only becoming more impressed with the product but also, as I try more ambitious tasks, I’m hitting some, hopefully, beta edition teething problems. I would use the “frown” button but unfortunately it’s determined to force me to use and/or setup an Outlook Profile; too many steps, easier to blog about it 🙂

The example I picked this time is a spreadsheet response to an Irish Government FOI (Freedom Of Information) request on expenses paid within a department. The result is typical of many reporting tools, repeating column values blanked out to make it “easier to read” but also making it difficult to re-analyse. In essence, the report contains a flattened view of four tables, Payee, Claim, Claim Items & Claim Item Details. The task I set myself was to separate out the four tables and link them back together using a PowerPivot Data Model.

And here’s the M code ( and here’s an Excel 2013 workbook version of the example):

 Source = Excel.CurrentWorkbook(){[Name="foitable"]}[Content],
 SourceTable = Table.AddIndexColumn(Source,"RowNo",1),
 AddPayeeID = Table.FillDown(Table.AddColumn(SourceTable,"PayeeID", each if [Full Name] = null then null else [RowNo]),"PayeeID"),
 AddClaimID = Table.FillDown(Table.AddColumn(AddPayeeID,"ClaimID", each if [Claim Date] = null then null else [RowNo]),"ClaimID"),
 modSource = Table.FillDown(Table.AddColumn(AddClaimID,"ClaimItemID", each if [Start Date] = null then null else [RowNo]),"ClaimItemID"),
 PayeeTable = Table.SelectRows(Table.SelectColumns(modSource,{"Full Name","Grade","PayeeID"}), each [Full Name] <> null),
 ClaimsTable1 = Table.SelectRows(Table.SelectColumns(modSource,{"Claim Date","Payment Date","ClaimID","PayeeID","Status","Total Claimed","Amount Paid"}), each [Claim Date] <> null),
 ClaimsTable = Table.TransformColumns(ClaimsTable1,{{"Total Claimed",each if _ = "NULL" then 0 else _},{"Amount Paid",each if _ = "NULL" then 0 else _}}),
 ClaimsItemsTable = Table.SelectRows(Table.SelectColumns(modSource,{"Start Date","ClaimID","PayeeID","ClaimItemID","Expense Type","Euro Line Amount"}), each [Start Date] <> null),
 ClaimsItemsDetailsTable = Table.SelectRows(Table.SelectColumns(modSource,{"ClaimID","PayeeID","ClaimItemID","Quantity","Unit","Net Total (EUR)"}), each [#"Net Total (EUR)"] <> "NULL")

..again a fraction of the time of doing this using say Python and much more approachable for those with limited programming skills. To create a single table query from the above”foitables” query, simply create a new DE query with “source = foitables[tableNameRequired]”.

But, you need to be careful how you do this, otherwise you can easily lose work and have to repeat some import tasks again & again. So here’s a list of gotchas I encountered:

Gotcha No. 1

Don’t connect your PowerPivot internal table to a DE Workbook connection directly. At least, not until you’re sure you’ve no further changes to make to the DE “code”.

If you modify the code, in any way, the connection is no longer valid (looks like the code is attached as a Base64 encoded string to the connection) and all your hard(ish) work within PowerPivot will be lost.  Even if not adding DAX formulas, you will have to do work on all imported DE tables, not just creating relationships, but all fields are imported as text, so dates & currencies etc. will need to have types applied.

Only code changes, not normal refreshes, cause this destroy-the-connection action to happen. Although I’d isolated the 4  for-import tables from the likely-to-change multi-table source “record”, it was no matter, change the one-step-removed query or a for-import query, and the connection is toast.

How to get around this? Well, as stated above, you could just wait until the M “model” is finalised/tested before doing anything outside DE. If you’re using Excel 2010, then creating a “linked table” rather than using the connection directly will get around this, but you’ll end up with the usual “linked table” refresh awkwardness.

If you have 2013, then the solution is much better, again don’t use the connection directly, but use instead the “load directly to Data Model” option at DE design stage. This seems to create a different type of internal PowerPivot connection which is obviously related to the DE output table and which continues to be valid even after code changes are applied.


The other no-no, is to avoid messing with DE connection’s “SQL”, in fact even looking at it can cause problems (that might be an exaggeration, it was late). The default “Select * from [Query1]” is about the limit of it and even it it, if “touched” but left as the same code, causes the query to return no columns, or in some cases, only 1 column, go figure? This strange behaviour can be seen in both 2010/2013 PowerPivot. In Excel 2010, direct connections using a workbook table appear not to work at all. Again, Excel 2013 seems to be more stable, for example if you pick the “All DE Queries” connection as a table source in Excel 2010 it’ll crash Excel, while in 2013 it gives an error message.


The Refresh button on the Table Query Ribbon (or a implicit refresh when leaving M design mode) breaks the Excel calculation graph. If you’ve referenced the DE table in a formula (such as using Table1[#All]) the formula will be “nuked” with the dreaded #ref, meaning you’ll have to go back an amend each one. This doesn’t happen if you use the Data Ribbon’s Refresh or RefreshAll buttons. So again, make sure the M code is “working” before linking to it in a formula, and even then, do not use the now-working-table’s Table Query Refresh button, ever, ever again!


M’s back – Create a PowerPivot Time Dimension using Excel’s PowerQuery

When I first heard of PowerQuery (aka Data Explorer in the past), the new ETL-like addin for Excel 2010/2013, I was only so-so impressed; likely to be yet another UI based ETL tool that does the easy bits, and one or two “packaged” hard bits, but  chokes when a real hard-case needs to be tackled!

That was before it became clear, that behind the UI facade lay a powerful and elegant functional language called M (yep, the same M that first appeared in Project Oslo). If data shaping & cleansing is what you do, this you’ve got to see.

To try it out, I picked on that perennial of BI data models, the Date (aka Time) Dimension. If you do any serious work with PowerPivot you’ll at some stage need such a table, for why, see here. I used the same variation on an old SQL trick as in creating a Date Dimension using DAX, but this time in M.

    dayT=Table.FromList(days,each {_},{"Day"}),
    monthT=Table.FromList(months,each {_},{"Month"}),
    yearT=Table.FromList(years,each {_},{"Year"}),
    dayJ = Table.AddColumn(dayT, "joiner", each 1),
    monthJ = Table.AddColumn(monthT, "joiner", each 1),
    yearJ = Table.AddColumn(yearT, "joiner", each 1),
    addDate=Table.AddColumn(yearmonthday,"Date",each let x = try #date([Year],[Month],[Day])
                                                     in if x[HasError]
                                                     then #date(1,1,1)
                                                     else x[Value]),
    removeErrors=Table.SelectRows(addDate,each not([Date]=#date(1,1,1))),
    renameCols = Table.RenameColumns(cleanCols,{"Month","MonthNo"}),
    addtextDate = Table.AddColumn(renameCols,"TextDate",each Date.ToText([Date],"YYYYMMDD")),
    addQtr = Table.AddColumn(addtextDate,"Qtr",each "Qtr" & Text.From(Date.QuarterOfYear([Date]))),
    DateDim = Table.AddColumn(addQtr,"Month",each monthLU{[MonthNo]-1})

PowerQuery’s Table function library appears not to have a cross join, that’s why I needed to add a “joiner” column to the three tables, and it took me a little while to get my head around M’s error handling; but other than that, even with my current elementary knowledge of M and DE’s function library, it was both a quick & a friction-free exercise (helped by the step by step try/feedback build-loop, very similar to working a problem with Excel).

If Data Explorer (aka PowerQuery) is new to you have a look at Chris Webb’s excellent series of articles on the subject.