Loading a File from OneDrive Personal into Power BI
Desktop

I am sure you know, there are quite a few companies that provide
online document storage for free, including Dropbox (which is my
personal favourite) but also Google Drive and OneDrive Personal
(Microsoft). This article today shows how you can load data from
OneDrive Personal (stored in the cloud) directly [...]


Read More »

Delaying Power BI Dataset Refresh Until The Source Data Is
Ready

This week a customer came to me with the following problem: they
had scheduled the refresh of their dataset but their source data
wasn’t always ready in time, so the old data was being loaded by
mistake. The best solution here is to use some kind of external
service (for example Power Automate) to poll the data source
regularly to see if it’s ready, and then to refresh the dataset
via the Power BI REST API when it is. However, it got me thinking
about a different way of tackling this: is it possible to write
some M code that will do the same thing? It turns out that it is,
but it’s quite complicated – so I don’t recommend you use the
code below in the real world. Nevertheless I wanted to write up the
solution I came up with because it’s interesting and you never
know, it might be useful one day.

Here’s the scenario I implemented. Using a SQL Server data
source, let’s say that you want to load the result of the
following SQL query against the AdventureWorksDW2017 database into
Power BI:

SELECT DISTINCT [EnglishDayNameOfWeek] FROM DimDate

However, let’s also say that the data in this table may not be
ready at the time when dataset refresh is scheduled; when the data
is ready, the sole value in the sole column of another table,
called ContinueFlagTable, will be changed from False to True:

Therefore when dataset refresh starts we need to check the value
from the ContinueFlagTable regularly; if it is False we need to
wait for a given amount of time and then check again; if it is True
the data can be loaded; if the flag hasn’t changed to True after
checking a certain number of times then we need to raise an
error.

Here’s the full M code of the Power Query query:

let
  //The SQL Server database
  Source = Sql.Database(
      "MyServerName", 
      "AdventureWorksDW2017"
    ),
  //The query to load into the dataset
  ResultQuery
    = Value.NativeQuery(
        Source, 
        "SELECT DISTINCT [EnglishDayNameOfWeek] FROM DimDate"
      ),
  //The number of times to check the Continue flag
  MaxIterations = 3,
  //The number of seconds to wait before checking the Continue flag
  DelaySeconds = 5,
  //Check the Continue flag
  Iterate = List.Generate(
      () => 1, 
      each (
      //Query the ContinueFlag table
      //after waiting the specified number of seconds
      //and keep trying until either it returns true
      //or we reach the max number of times to check(Function.InvokeAfter(
          () => 
            let
              dbo_ContinueFlag
                = Value.NativeQuery(
                    Source, 
                    "SELECT [ContinueFlagColumn] from ContinueFlagTable where -1<>@param", 
                    [
                      param
                        = _
                    ]
                  ),
              Continue
                = dbo_ContinueFlag{0}[ContinueFlagColumn]
            in
              Continue, 
          #duration(
              0, 
              0, 
              0, 
              DelaySeconds
            )
        )
        = false
      )
        and (_ < MaxIterations)
      ), 
      each _ + 1
    ),
  //Find how many times the ContinueFlag was checked
  NumberOfIterations
    = List.Max(Iterate),
  //Did we reach the max number of checks?
  ReturnError
    = NumberOfIterations
      = MaxIterations
      - 1,
  //Table type of the table to load into the dataset
  ReturnTableType
    = type table [
      EnglishDayNameOfWeek = Text.Type
    ],
  //Error message to return if the
  //max number of checks is reached
  ErrorMessage
    = error "After waiting "
      &amp; Text.From(
        MaxIterations
        * DelaySeconds
      )
      &amp; " seconds your data is still not ready to be loaded",
  //Handle Power BI Desktop's behaviour of asking for
  //the top 0 rows from the table
  //before loading the actual data
  OverrideZeroRowFilter = 
    if ReturnError then 
      ErrorMessage
    else 
      Table.View(
          null, 
          [
            GetType = () => 
              ReturnTableType, 
            GetRows = () => 
              ResultQuery, 
            OnTake
              = (
                count as number
              ) => 
                if count = 0 then 
                  #table(
                      ReturnTableType, 
                      {}
                    )
                else 
                  Table.FirstN(
                      ResultQuery, 
                      count
                    )
          ]
        )
in
  OverrideZeroRowFilter

There’s a lot to explain here:

  • Everything is in a single query, and this is deliberate: I
    found it was the only way I could make sure that the query to load
    the data (ie the query in the ResultQuery step) is run only once,
    after the check on the ContinueFlagTable has returned true. If I
    split the code to get the data off into a separate query, I found
    that it got queried before any checks on ContinueFlagTable; this
    seemed to be something to do with the formula firewall, but I’m
    not sure.
  • I’ve used List.Generate
    to do the polling of ContinueFlagTable. There’s a good example of
    how to use it to implement Do Loops here.
  • I’ve used
    Function.InvokeAfter
    to add the delays in between checks on
    ContinueFlagTable.
  • I found that if I ran the same query to check
    ContinueFlagTable, after the first execution the result was cached
    and the query couldn’t tell if the flag changed after that.
    Therefore I wrote a SQL query with a WHERE clause that was
    different each time but which always returned true; I did this by
    passing the iteration number into the query via a parameter, and I
    passed the parameter in using the
    Value.NativeQuery
    function.
  • It’s possible to raise an error when needed using the error
    keyword, described here.
  • The OverrideZeroRowFilter step at the end uses the technique I
    blogged about
    here
    to deal with the fact that Power BI Desktop asks for the
    top 0 rows from a table to get the schema before it loads the
    data.

Refreshing the table in Power BI Desktop with the value True in
ContinueFlagTable loads the data successfully; in Profiler you can
see one query to ContinueFlagTable and one query to get the data to
be loaded from the DimDate table:

Refreshing the table with the value False in ContinueFlagTable
shows the specified number of queries to ContinueFlagTable and no
query to DimDate:

Here’s the error message you get in Power BI Desktop when this
happens:

Here’s the same error in the Power BI Service when you try to
refresh the dataset set:

How to use the USERRELATIONSHIP Function to activate In
Active Relationship in Power BI

USERRELATIONSHIP Function to activate the In Active Relationship in
Power BIThe USERRELATIONSHIP Function can be used as filter
argument in CALCUALTE to activate the inactive relationship between
two tables, for that calculation.Syntax
:USERELATIONSHIP(<columnName1>,<columnName2>) Remarks
:USERELATIONSHIP can only be used in functions that take a filter
as an argument, for example: CALCULATE,

How to Create Virtual Relationships using TREATAS Function
in Power BI DAX

How to Pass or Propagate filters from a unrelated table column
values as input using TREATAS Function in Power BI DAXThe TREATAS
Function applies the result of a table expression as filters to
columns from an unrelated table. It treats the columns of the input
table as columns from other tables. For each column, filters out
any values that are not present in its respective output column.It

How to Create a Top N Sales Summary in Power BI DAX

Power BI DAX to Create a Top N Sales Summary Table Suppose, we have
the Data Model as follows :We have the sample Sales data by Year
and Product as follows :Scenario :Now we want to calculate the Top
3 Sales by Product per year from the above data. Also, the Sales
which does not falls in Top 3 needs to group and show in a new row
as "Others".This can be done using the following DAX Table

How to refresh Power Queries on protected sheets in
Excel

When working with Power Query in Excel you might want to refresh
Power Queries on protected sheets. But this will not work by
default. Using a macro to temporarily unprotect the sheet and
protect it again will do the trick. But this requires the password
being displayed in the VBA code. So please have in mind that this
technique only works for scenarios where you want to prevent
accidental changes with the password protection.

Steps to refresh Power Queries on protected sheets

The following VBA code will unprotect the sheet “mySheet”,
then refresh the query “myQuery” before protecting the sheet
again with the password “myPassword”.

Sub RefreshmyQuery()
Sheets("mySheet").Unprotect Password:="myPassword"
ActiveWorkbook.Connections("Query - myQuery").Refresh
Sheets("mySheet").Protect Password:="myPassword"
End Sub

But if you use it as it is, you’ll receive the following error
message:


The re-protection of the worksheet will kick in sooner than the
refresh could finish.

To overcome this, you have to disable background refresh of your
Power Query (“myQuery”). This can be done via the properties
like so:


Disable background refresh then:

That’s it. Refresh will succeed.

Enjoy and stay queryious 😉

The post
How to refresh Power Queries on protected sheets in Excel

appeared first on The
BIccountant
.

Updated Look for External Guest Users in Power BI (And a way
to see the Navigation Pane)

I am fortunate to work across multiple Power BI Tenants, in some
cases I have a local account and in other instances I work as an
External Guest user. The first thing that I noticed when logging in
is that there is an updated look...

The post
Updated Look for External Guest Users in Power BI (And a way to see
the Navigation Pane)
appeared first on Reporting/Analytics Made easy with
FourMoo and Power BI
.