Build Scalable BI Solutions Using Power BI and
Snowflake

On the Power BI team we want our customers to be successful
whichever data source they’re using – not just the Microsoft
ones. Recently I had the pleasure of recording a webinar on the
subject of best practices for using Power BI with Snowflake, along
with Craig Collier from Snowflake and Chris Holliday from Visual BI
(a partner that specialises in Power BI/Snowflake solutions). You
can watch it here:


https://info.microsoft.com/ww-landing-build-scalable-BI-solutions-using-power-BI-and-snowflake.html

There’s a summary of what we talk about on the Snowflake
blog:


https://www.snowflake.com/blog/maximizing-power-bi-with-snowflake/

We’re continuously improving the performance and functionality
of all our connectors, and Snowflake is no exception: we recently

announced
that in early 2021 we’ll have support for Snowflake
roles and the ability to use your own SQL queries in DirectQuery
mode. Power BI support for
Azure Service Tags
(on the roadmap
here
) will also be very useful for Snowflake customers.

Join Us! Power BI Dev Camp! Thursday 29 October,
2020

This month’s Power BI Dev Camp topic is: Deep Dive into the Power
BI JavaScript API. Learn how to become a pro when developing with
the Power BI JavaScript API. In this camp session, you’ll learn
how to move beyond embedding read-only reports to provide your
users with the ability to customize existing reports and to create
new reports on top of existing datasets. The session will
demonstrate how to implement interactive capabilities with Power BI
embedding including navigating between pages, setting filters and
applying bookmarks.

Calculation Groups with Disconnected Tables in Power
BI/AAS

Below I will explain how to use Calculation Groups with
Disconnected tables. I know that some of this might be able to be
done with other calculation groups. I find I have more flexibility
when combining Calculation Groups with a disconnected table. Below
are some...

The post
Calculation Groups with Disconnected Tables in Power BI/AAS

appeared first on Reporting/Analytics Made easy with
FourMoo and Power BI
.

A Function To Visualise Parallelism In Power Query
Diagnostics Data

Most of the time I’ve spent looking at Power Query Query
Diagnostics
data, I’ve been looking at data for a single
query. Over the past few days though I’ve spent some time
investigating what this data shows for all the queries that are
executed for a single dataset refresh. To help me do this I wrote
the following M function:

(InputTable as table) => 
  let
    Source = InputTable,
    EarliestStart = List.Min(Source[Start Time]),
    AddRelativeStart = Table.AddColumn(
        Source, 
        "Relative Start", 
        each [Start Time] - EarliestStart
      ),
    AddRelativeEnd = Table.AddColumn(
        AddRelativeStart, 
        "Relative End", 
        each [End Time] - EarliestStart
      ),
    SetToDurations = Table.TransformColumnTypes(
        AddRelativeEnd, 
        {
          {"Relative Start", type duration}, 
          {"Relative End", type duration}
        }
      ),
    CalculatedTotalSeconds
      = Table.TransformColumns(
          SetToDurations, 
          {
            {
              "Relative Start", 
              Duration.TotalSeconds, 
              type number
            }, 
            {
              "Relative End", 
              Duration.TotalSeconds, 
              type number
            }
          }
        ),
    GroupedRows = Table.Group(
        CalculatedTotalSeconds, 
        {"Id", "Query"}, 
        {
          {
            "Relative Start", 
            each List.Min([Relative Start]), 
            type number
          }, 
          {
            "Relative End", 
            each List.Max([Relative End]), 
            type number
          }
        }
      ),
    MergeColumns = Table.AddColumn(
        GroupedRows, 
        "Id Query", 
        each Text.Combine({[Id], [Query]}, " "), 
        type text
      ),
    IdToNumber = Table.TransformColumnTypes(
        MergeColumns, 
        {{"Id", type number}}
      )
  in
    IdToNumber

If you invoke this function and pass in the “Detailed” query
diagnostics data query:

…you’ll end up with a query that gives you a table that
looks something like this:

This table has one row for each Power Query query that got
executed while the diagnostics trace was running, an Id column to
uniquely identify each execution, the name of the query executed,
an Id Query column that concatenates the previous two columns, and
Relative Start and Relative End columns that give you the number of
seconds from the start time of the first query executed up to the
start time and end time of the query on the current row.

Loading the data into Power BI allows you to build a stacked bar
chart with Id Query on the axis and Relative Start and Relative End
together in the values:

If you then set the Sort By Column property of Id Query to the
Id column (which is numeric, so this ensures that the values in Id
Query are sorted correctly) and set the Data Color property of the
Relative Start values to white (or whatever the background of your
chart is) so that it’s invisible, then finally set the axis of
your stacked bar chart to be sorted by Id Query in ascending order,
you get a visual something like this:

This is a kind of Gantt chart where the x axis shows the number
of seconds since the start of the first query execution and each
bar shows the start and end times of each query, making it easy to
see which queries are executing in parallel.

“What can this show us?” I hear you say. Well, that’s
something for a future blog post. All that I can say right now is
that it’s COMPLICATED and I don’t properly understand it myself
yet. But it’s undoubtedly interesting.

Convert DateTime to ISO 8601 date and time strings in Power
Query

Often, when querying APIs it is require to enter date and time
filters in ISO
8601 format
. Today I show a quick way to convert DateTime to
ISO 8601 string, based on an ordinary DateTime field according to
the following pattern:

2020-10-11T15:00:00-01:00

This represents the 11th October 3pm in UTC -1 timeszone.

Steps to convert DateTime to ISO 8601

If I enter:

#datetime(2020,10,11,12,0,0)

into the formula bar, it will be converted to :

11/10/2020 12:00:00

Comparing to the desired ISO format the year, month and days are
in the wrong order. So using the universal Text.From
function will not return the correct result.

Fortunately, there are a couple of xxx.ToText function in Power
Query that allow for dedicated formatting parameters in their 2nd
arguments. For example, the function DateTime.ToText
can actually return the string in the desired format if you pass a
format string as the 2nd parameter:


Convert DateTime to ISO 8601

DateTime.ToText with format parameters

The syntax for these format strings can also be found here.

Last step is to add the time-zone string ( & “-01:00”),
as I’ve started from a DateTime value only:

DateTime.ToText(DateTime.From(#datetime(2020,10,11,12,0,0)),
"yyyy-MM-ddThh:mm:ss") & "-01:00"

Enjoy & stay queryious 😉

The post
Convert DateTime to ISO 8601 date and time strings in Power
Query
appeared first on The BIccountant.

Power Query (M) – Passing Parameters dynamically to a SQL
Server Query

I had a requirement where the client wanted the capability to
decide how much data to load from a SQL Server Query (TSQL). This
was so that they could limit the datasetreturned, as at times they
did not need all the data. So below I...

The post
Power Query (M) – Passing Parameters dynamically to a SQL Server
Query
appeared first on Reporting/Analytics Made easy with
FourMoo and Power BI
.