Custom Connector to import Google Sheets with OAuth2 authentication in PowerBI

Recently I came across the need to connect to Google Sheets with a secure authentication process quite often, so I will share with you how and to what extend I got the custom connector working that I found here. It uses OAuth2 authentication, so you can share your workbook with selected colleagues and they will be prompted to enter their credentials in Power BI if they try to access these files.

Edit: As it turns out, the credentials work for all Google accounts. So you can download my .mez  and simply paste it into your Custom Connectors-path without touching Visual studio ( create path: [My Documents]\Microsoft Power BI Desktop\Custom Connectors ). I believe this will work for the 1st 100 users and then you have to create you own. But if you want to use it in production, I’d strongly recommend to create your own anyway (otherwise continue with section “Use Google Sheets Data Connector in Power BI Desktop”) :

Setup Google API

Go to the Google Developer API and if you don’t have a project yet, just create one:

Go to “Credentials” -> Create credentials and choose “OAuth client ID”:

Choose “Web application”, adjust the “Name” if you like and paste the redirect-url into “Authorized redirect URLs”: https://preview.powerbi.com/views/oauthredirect.html

This will return the client ID and secret for your connector:

 

Adjust the connector in Visual Studio

Read more

How to create and use an R-function-library in Power BI

Edit 10-10-2017: There is also a (simpler) way to run a custom function library described here: http://www.thebiccountant.com/2017/10/06/create-a-function-library-in-power-bi-using-m-extensions/ . If you go that route, the only point of interest in the article might be how to create your function library automatically.

Once you’ve discovered the huge potential R gives you to expand your analytical toolbox in Power BI (check some tips & tricks in my previous blogpost if you haven’t already), you might wish to have all your awesome functions conveniently at hand when designing new solutions. And thanks to M, there’s actually nothing easier than that: R-function-library in a record (which works just the same for M-functions 🙂 )

Put your functions into a record (fnr) with the function name as the field name and the function itself as the value: One query to hold them all (and not cluttering your editor pane) and ready to use as if they were native functions:

R-function-library

Use

will export content of my query “Actuals” to csv-file on my desktop.

  1. fnr is the name of the record. You can give it your own name of course, I prefer to keep this as short as possible.
  2. followed in square bracket is the name of the function (record field name)
  3. in ordinary brackets you have the function arguments just like in standard M (record value)

Create record

Read more

Tips and Tricks for R scripts in the query editor in Power BI

Especially if you are new to R, there are some things one needs to know to successfully run R-scripts in the query editor of Power BI. I will share them here along with some tricks that made my R-life in Power BI easier:

How to get started – useful links:

Input:

You can feed multiple tables into the R-script

If you click the icon “R script”, the table from the previous step will automatically be passed as the “dataset” to the R-script. So if you don’t fill in any R-code, this will happen:

image

But if you need the content from other tables as well, you just add them into the square brackets like this:

image

“Documentation” looks like this:

image

You can use parameters in the R-script

Apart from tables, you can also use text strings as parameters in the script. They need to be inserted into the code with preceeding “& and trailing &”:

RExportCsv= R.Execute(“write.csv(dataset,” “&CsvExportPath&” “)”,[dataset=Actuals])

Beware that they must be text. So if you want to pass a number, wrap it into Text.From(…).

You cannot use anything else apart from tables and parameters in the R-script

Well, at least I haven’t managed it Smile

R-life gets easy-peasy if you use M-functions for your R-script

Read more

How to import from Excel with cell coordinates in Power Query and Power BI

There might be occasions where you want to import data from Excel into Power Query or Power BI using cell coordinates like a range from E3 until G9 for example (“A1 cell reference style”). The function I provide below also caters for the potential pitfalls of this task that Maxim Zelensky has described in his article.

Background

If your worksheet has one leading empty row and column, the import will ignore them and automatically return the range starting from B2. So to fetch the range E3:G9 you have to delete the first row and the first 3 columns. But as Maxim has found out, remaining formats on empty cells will lead to an import of empty rows and columns. So the number of rows and columns to delete will vary and is hard/impossible to predict.

Method

The range that PowerQuery or PowerBI will import is stored in the Excel-file already in the sheet-data and the xml looks like this (“Sample3” from Maxims data):

The imported range is E1 till J12, as the first rows contain formatting instructions, and will therefore be imported as well. In the 3rd row E3 shows up with the first value, which is surrounded by “<v>”.

This is how it looks like in the Xml.Table in the query editor:

Task is to calculate the number of rows and columns delete, considering the individual offset that is caused by the formatted empty cells.

Code

So I’ve cooked together these ingredients in a pretty massive code that you can download here: fnImportFromExcelCellCoordinates.txt

How to use the function

Read more

Should we pipe M?

“Just because you could doesn’t mean you should”… So I’m asking the Power Query and M fans & experts here if we “should” pipe M:

Background: With M you can nest your expressions like in Excel to group commands that belong together. But this has some disadvantages like:

  • Reading:
    • the execution order of the functions doesn’t match the reading order
    • the function name and the arguments are torn apart
  • Writing:
    • if you write an additional function around the existing expression which then fails, it is very laborious to manually delete all the code to go back to the previous state (especially, if you have trailing function arguments)
    • if you later recon that you need an intermediate step of the nested expression and need to split up the statement, the same problems occur

So instead of this code:

we could write it like so:

This code works in M if you have a record (“M”) in your queries that contains Kim Burgess’ cool code and an additional record (“M”) that he has kindly helped me with:

All that still folds!

Honestly, it doesn’t look pretty in my eyes (yet), but it works and eliminates the disadvantages mentioned above. With some help of Expression.Evaluate, we could further clean it up to match the magrittr-style for example, but I’ve been warned to use this function, so not sure what to prefer at the end.

Please let me know your thoughts & stay queryious 😉

File to download: ShouldWePipeM.zip

How to expand a column that cannot be expanded in Power BI and Power Query in Excel

Especially when working with JSON-data, you might end up with a column that has elements of mixed types in it. The expand column – arrows will be missing, but some elements still need to be expanded, like here:

But there is an easy way to fix it:

Transform to expandable column

Table.TransformColumns(Source, {{“Column1”, each if Value.Is(_, type list) then _ else {_} }} )

It transforms the “Column1” from table “Source” by checking, if the content of the each row ( _ ) is of type list and if yes, keep that value ( _ ) and if not, transform it to a list (by framing it into curly brackets {_} )

Syntax for tables

Table.TransformColumns(Source, {{“Column1”, each if Value.Is(_, type table) then _ else #table({“Column1”}, {{_}} ) }} )

Syntax for records

Table.TransformColumns(Source, {{“Column1”, each if Value.Is(_, type record) then _ else [a=_] }} )

File for Subscribers to download: HTExpandColumnThatCannotBeExpanded.zip

This technique should be applied to columns where the expandable elements all have the same structure. If that’s not the case, you should use this technique instead.

Enjoy & stay queryious 😉

Non-linear Break-Even Analysis in PowerBI

A break-even analysis tells you at which value of the parameter in question your profit-calculation will turn positive (link). Here we need to sell at least 173 at a given price of 20 before we’ve recovered all our costs:

If your variable costs are constant, you can solve it by this formula:

BreakEvenQuantity = Total Fixed Costs / (Unit Sale Price - Unit Variable Costs)

You’ll find tons of examples on how to do this in Excel like here .

Non-linear cost structure

But in real life, the variable costs often depend on certain quantities as you get discounts for purchasing large amounts. The following table shows a cost structure with fix costs in row 1. The 3rd column “FixOrQty” indicates if the cost item is fix or dependent on the quantity (Qty). The 2nd row contains a variable cost that is constant with 2 for all quantities. Row 3&4 show a variable cost of 8 for quantities up to 100 and if you purchase more than 100 the costs will be lowered to 2 for all additional quantities. Row 5-7 have a similar structure, but with 3 quantity ranges:

 Solve with goal-seek algorithm

Read more

Guide for switching Signs in Power BI and Power Pivot (bypassing Unary Operators in DAX)

In finance & accounting, you very rarely report the figures with the signs of their source systems, but switch (certain) signs according to different needs. Instead of using unary operators for it, I’ll present an easy and dynamic way for it in Power BI and Power Pivot using DAX. It will cover the following 3 main scenarios:

  • 1_SwitchAll: All signs are switched (red)
  • 2_SwitchExpLiab: Expenses and liabilities are switched back to their original values (green)
  • 3_BWT_Indiv: Only the main figure for expenses (or liabilities) carries a minus, all following positions specifying the expenses are (principally) reported as positives (blue)

 

Switching signs in Power BI and Power Pivot without unary operators

I’m using the sample data from this article but changed the source-data to a double-bookkeeping structure. There signs are used and the transaction entries in your ledger table always add up to zero. This is a method that prevents errors when posting and can also be used to prevent errors in reporting. If you keep the signs in your reporting system, all you have to do is add up the relevant figures and the returned (absolute) figures will always be correct. If you have read my previous articles on Easy P&L, you have seen this method in action: No minus-operation there, just a simple stupid adding of all accounts who fall into several (sub-) total categories via the bridge-table.

The Account-table also contains of (sub-) totals and the column “AccountType” shows if the positions are regarded as Turnover (Revenue) or Expenses:

Table “Accounts”

1_SwitchAll

My values on “1_SwitchAll” corresponds to “FinalValue” in the article above. The revenues come from consultancy and coursed provided. But the revenue for courses don’t just consist of attendee rates, but the costs for catering and paid instructors shall be deducted (highlighted in yellow). So the “good” numbers that contribute to cash in your pocket shall be reported without a sign and the “bad” numbers that result in an outflow of cash shall be reported with a minus. Within the expenses category, the costs carry a minus and the travel refunds (highlighted in orange), which are cash positive, are reported as positives.

2_SwitchExpensesLiabilities

Another requirement that is often used for balance-sheet-reporting or reports that only report on cost-situations, require that the costs or liabilities are reported without signs. … Principally, because the reimbursements/cost deductions shall be reported with an opposite sign (to show the adverse effect to the cashflow). This is what “2_SwitchExpLiab” shows (not covered in the article).

3_BWT (“BossWantsThat”)

Last but not least comes a typical “BossWantsThat”-requirement: Basically some strange stuff that you just have to deliver. Here the main categories “Revenues” and “Expenses” shall be shown with the signs that reflect the cash-direction, but all specifications that follow below shall be reported without signs (again: Principally, because positions with opposite cash-effects than the main category shall carry inverted signs).

Reporting techniques covered with this approach

Read more

Automatical or Bulk- Rename Columns in Power BI and Power Query

Edit 7th Feb 2017: Friendly reader Roknic pointed out in the comments below that there’s actually an existing function for it in M: Table.TransformColumnNames 🙂

So the first of my example below would actually look like this:

Table.TransformColumnNames(Source, each Text.Replace(_, " ", "_"))

But still keeping my original post here, as the transformations in them might help for other use cases:

If you want to rename all of your table’s columns with a common rule, like “replace all spaces by underscore” or just “delete all spaces”, check out this easy method:

The above formula will replace all spaces (” “) by underscores (“_”).

How does it work:

The 2nd argument in the Table.RenameColumns-formula is a list of lists, just like in Table.TransformColumnType from this article. So we apply the same technique here: List.Transform transforms a single element from a list into a list-item, whose 2nd argument will be calculated with a Text.Replace-function.

Variations:

Rename Columns Variations

Only replace FirstN or LastN elements from the column names:

Read more

Conditions in FirstN, LastN and other xN-functions in M, PowerBI and Power Query

Today I discovered that we can use conditions in many of the N-selecting functions where one/I would normally expect just a number-expression for the N:

Table.RemoveFirstN( table as table, optional countOrCondition as any)

So apart from being able to select a certain number of rows to be removed, we can pass a condition (as function). This condition will iteratively be checked for every row in the table (from top or bottom) and as long as every (next) step returns true, the resulting range will be removed. So as soon as one row breaks the condition, the process will stop.

I find that totally awesome, as we can now remove all top-rows who have an empty field in Column3 like this for example:

Table.RemoveFirstN(<MyTable>, each each (_[Column3] = null or _[Column3] = “”))

Yes, this will remove the first sequence of consecutive nulls in the table. So all other rows with nulls in the table coming later after a non-null value has “broken in”, will remain.

This is the list of function, where you can use this M-agic:

Enjoy & stay queryious 🙂