Posts Tagged ‘PowerQuery’

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

New M-function: Table.TransformColumnTypesToFirstRowsTypes for PowerBI and PowerQuery

The following function automatically transforms all columns to the types that have been detected in the cells of its first row. Provided they come as: Number, date or text (but you can add additional type conversions if you need them.).

It also has some rough edges: If the first value is empty, the column will be converted to text. Also, it contains the (improved) logic from this article:  So if a date is written in a way that it could also be a number, then it will be converted as a number. To minimize the room for errors here, I’ve converted the values to text first, but this is still something to watch out for. But in very many cases it will just do what you have long been looking for:

Use cases:

  • You don’t want to use the automatic but static/hard coded type-conversion in the 2nd step (because you know you’re table is going to have more columns in the future and you want to cater for proper type-conversion of them as well)
  • You’ve lost your column types due to some other command (like Table.ReplaceValues)

M-Code

Code to download: TableColumnTypesToFirstRowsTypes.txt

 

Enjoy & stay queryious 😉

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 😉

Table.TransformColumns – alternative in PowerBI and PowerQuery in Excel

This article shows a trick for a little problem that annoyed me for quite some time: How to get Table.TransformColumns transforming the values of a column with a reference to a value (from the same row) of another column?

1 Replace text by a value from another column

So instead of adding a new column where the “*” is replaced by the value from column “WhildcardValue”, I just want to perform the replacement-operation in the original “Text”-column, so that I don’t have to rename and delete the other columns later:

So far, I always ended up fighting with Table.TransformColumns-function and got quite frustrated because I couldn’t find a way to reference the (row-) value of the other column. As it turns out, I was fighting the wrong target here, because Table.ReplaceValue is actually the saviour for this challenge:

1.1 Check column “Text” -> Transform -> Replace Values

So in (4) we just filled in a dummy-value, which we’re going to replace with a reference to the desired column: “each [WildcardValue]” like this:

Read more

SQL-query folding bug still alive and sucking in PowerBI and PowerQuery in Excel

Edit: There is currently no interest at Microsoft to change the current behaviour: http://community.powerbi.com/t5/Issues/Bug-Query-folding-not-working-with-non-SQL-datasource/idi-p/225100#M11611 .

I found that this workaround works also pretty well (faster in most cases), as long as your filter-tables are not too long: http://community.powerbi.com/t5/Desktop/Parameterized-SQL-Query-with-query-folding/td-p/171503

Are your SQL queries that filter with a non-SQL-table slow in PowerBI and PowerQuery in Excel? Then this might be of interest for you:

Nearly 2 years ago when I published my first blogpost about the bug that prevents query folding on SQL-sources when filtered by non-SQL-sources, PowerBI was still so new that I even didn’t mention it in my blogpost. I had enormous problems with some clients work with performance and spent 2-digit hours on figuring out the reason with some good help from the TechNet-Forum.

Today I was notified that “this bug was deemed as not high-pri enough to warrant a fix”. So it’s well alive and sucking: In Excel as well as in PowerBI. Considering how many people read my article, I found it worth to mention again and provide an improved solution. It is a function that’s easy to implement and takes the name of the tables and its key-columns as parameters:

Code to fix Query-Folding bug in PowerBi and PowerQuery

Code to fix Query Folding Bug in PowerBI and PowerQuery

And the code to copy: FilterSQLTable.txt

Hope this prevents you from wasting precious time.

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