Shaping vs Modelling in Power BI

Level: Beginners The objective of this article is to help you
understand the differences between shaping your data (using Power
Query) and modelling your data (using the modelling tools inside
Power BI). You will need to do both to build a robust Power BI
report.  I first wrote this article [...]

...

 

How to create DirectQuery (Composite) using an existing
Power BI Dataset and a local Excel file

If you are looking how to use DirectQuery (Composite) models in
Power BI I show you how to do this with an existing Power BI
dataset from the Power BI Service and then importing an Excel file
from SharePoint. In my working example I already have got a dataset
which contains the Power BI Audit Log data. I want to...

The post...

 

Exception reporting and management for missing fuzzy matches
in Power BI

In
a previous article
I have described how to use Fuzzy merge to
match misspelled data to valid categories. With the sample data at
hand, all values could be matched to existing categories. But what...

 

New Option To Solve Problems With Power Query Not Loading
All Data From An Excel Worksheet

Some interesting new Power Query functionality was released in
the December release of Power BI Desktop (it’s also in the build
of Excel I’m running) that didn’t get announced in the blog
post but which will be useful for anyone using Excel as a data
source. It is fully documented but I thought I’d blog about it
anyway to...

 

Extract only letters from a mixed string in Power Query and
Power BI

This is a quick method about extracting only letters from a
string. It is part of the Week2
“Preppin’ data” challenge
.

Task for extracting letters from a string

Image you have a string like so: “10.ROADBIKES.423/01” and
would like to extract only “...

 

Moving On From PASS. What’s Next?

The COVID-19 global pandemic was by far the biggest influencer
of 2020. This virus has cost over 1.8 million lives worldwide, and
has upended normal life and commerce for most everyone. Many
thousands of businesses were forced to close, costing millions of
jobs. For those of us in the data community, we learned of one
...

 

Clean up or harmonize mis- or differently spelled category
data with Power Query

A typical problem with data that has been created by manual
entries is that category values are often misspelled or missed. So
in this article I’m showing a very powerful technique on how to
deal with this problem to clean up dirty category data. It was
inspired by the “Preppin’ data” challenge whose instructions
you can...

 

How to refresh Excel files faster in Power BI

I was recently getting an error when refreshing an Excel file
that was stored in SharePoint online. This got me thinking what the
issue is and is there a way to improve it and YES there is as
detailed below. When I investigated this further,...

The post...

 

Your Oracle data import in Power BI and Power Query is
slow?

If you’re using the native
Oracle connector
in Power Query, you will probably experience a
very slow import performance. Thanks to Tristan Malherbe for
recommending to use the OleDB-connector in Power Query instead....

 

Handling Multiple URL Query Parameters With The Same Name
Using Web.Contents In Power Query/Power BI

Some time ago I wrote a pair of popular posts about using the

Query and RelativePath options of the Web.Contents function in
Power Query
and...