Converting PowerBI Desktop Reports from Import to Direct Query

TL;WR: PowerBI can either cache your data or query it live. If you build a report cached (import mode) it won’t let you convert it to live (directquery). I decided to do it anyway. Suggested Matching: ███████, soda, touch of ███████, ███████, and frozen fruit. Layered in a Collins glass.

PowerBI Desktop is…. in development (at the time of writing, hopefully forever). I’ve had all kinds of fun with its fantastic quirks.

Anyway I had a report with lots of formatting but very simple queries and relationships. The pbix files are binaries with zipped data in them, but I couldn’t be bothered reverse engineering the extra headers/wrappers. Instead I loaded up the report in question, used a debugger to edit the report definition in memory, and re-save it. After that fun, reloading and fixing the computed columns was easy and the new report works.
PowerBI wants to use its own zip wrapper, fine use it.

Obviously, that is a highly unsupported workaround and a Bad Idea™

I personally just used CheatEngine for this, I guess you could use WinDBG or IDA or any of the other ones if you’re familiar. CheatEngine is however perfect for simple application memory editing.

Updating Queries to use DirectQuery

1. Attach a debugger to the PowerBI desktop application.
2. Replace all instances of the following bytes:

44 69 72 65 63 74 51 75 65 72 79 01 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 14

with

44 69 72 65 63 74 51 75 65 72 79 01 00 00 00 00 00 00 00 01 00 00 00 00 00 00 00 14

Updating Relationships to use DirectQuery

There’s two ways of going about this:

1. Be smart and write a little asm patch that replaces any calls to read the memory location to read back from your own location.
2. Be lazy like me and let pbi desktop reinsert the overwritten IgnoreCase setting (reason being that you can’t just ‘add space’ to the memory of a loaded application for our new tag).

Since I am inherently lazy this is the second way.

1. Search the memory for the string below and replace it with the one after INCLUDING WHITESPACE (probably best to just edit in memory, whitespace out the rest of the bi: tags)

PreferOrdinalStringEquality=”true”>

PreferOrdinalStringEquality=”true” DirectQueryMode=”DirectQuery”        />

Now after saving and reloading (on a copy of the report obviously) PBI should ask to revalidate the queries and you’ll need to remove unsupported functions like calculate and datetime stuff. I personally lost my computed columns but everything else worked. If you search memory again PBI should have reinserted the IgnoreCase option in the relationships after the direct query option. If it didn’t…. well…. like I said. Bad Idea!

You could freeze the application with the debugger while you’re doing this, I didn’t and it worked regardless, but I take no responsibility whatsoever for editing open report definitions using a debugger.