Transform Column using custom function in Power Query (part 2)

Ivan Bondarenko December 12, 2016 16 Comments

Year ago I wrote a post Transform table column using own function in Power Query. According to stats, it is quite popular topic, and I’m not surprised.

However, I think that old post is quite complex and too long. Advice should be shorter.

In addition, that post do not cover another very important scenario, when you need to transform column using value from another column.

Consider following case

Task:

We need to Trim column [Name]
We need to divide [Amount] by [Counter] (and do not want to add additional column)

Let’s do this.

(more…)

Decode Active Directory field UserAccountControl using Power Query

Ivan Bondarenko December 3, 2016 4 Comments

Recently my colleague had to identify inactive accounts in Active Directory and use this data in automated report on regular basis, to quickly react on new switched off users.

There are plenty of VBA solutions or Vbscripts that work with Active Directory and pull data from it.

However, for automated reports developed in Excel or Power BI it will be more convenient to use Power Query (Get & Transform) to get data directly from AD. In addition this helps to reduce mirroring of data.

Information about user status is stored in field UserAccountControl.

According to documentation on MSDN, field value is not very user friendly

“To disable a user’s account, set the UserAccountControl attribute to 0x0202 (0x002 + 0x0200). In decimal, this is 514 (2 + 512).”

Resulting value is a sum of different values of multiple properties.

E.g. what 2146 could mean?

Not easy to say when number is in decimal notation.

However, it is much easier if value of UserAccountControl is represented in binary string – of 0 and 1.

2146 = 2048 + 64 + 32 + 2 = > 100001100010

For example, if we need to check property ACCOUNTDISABLE, we only need to check second digit (from right).

Unfortunately, there is no standard function in Power Query that converts decimal number to binary notation, so I had to create own function.

As usually, it is available on Github: https://github.com/IvanBond/pquery/blob/master/Number.ToBinaryString.m

// Number.ToBinaryString( 1026 )

// result: 10000000010

Function code is quite short, although it is recursive

When number is in binary notation, we can use Text.End, Text.Start function to get needed digit.

Just an idea. Can be used with Power BI alerts to unblock self-blocked users even before they call to IT, for audit, or with Microsoft Flow somehow…

Changed Type step in Power Query

Ivan Bondarenko November 22, 2016 2 Comments

My work is connected with data coming from different countries, which have different local formats of date, numbers etc.

To be able to consolidate data in Excel models, I have to convert data accordingly.

In Power Query and Get & Transform we can set default Locale in Query Options

However, when I load data from different sources and apply Changed Type ( Table.TransformColumnTypes ) operation I must use corresponding locale for argument “culture”, which is optional.

I noticed interesting behavior. When I change type

New step “Changed Type” appears in Applied Steps without gear

Of course, when Power Query engine created this step it used default Regional Locale, which is “en-US”, as a result values are wrong. I have to use Russian locale for this sample.

When I manually add argument “culture” in formula bar – gear appears near the “Changed Type” step:

It shows nice menu – Change Type with Locale

It even shows sample format when I select Locale

Conclusion

For me it is fine to add locale manually, but when I explained this to my colleagues they were not happy to hear this.

Maybe I don’t know something… But how to call this “Change Type with Locale” by default?

E.g. when I change Data Type – ask about locale I want to use.

Probably, worth to add something like checkbox “Always ask about locale when I change data type” in the area of Current Workbook options.

Or just display gear for step Changed Type by default to let user go the and select local from list.

There is also an idea with close meaning on Power BI uservoice forum

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/17155889-add-using-locale-option-to-new-data-type-menu-bu

UPD:

The option was found. Thanks to Frank and to Maxim Zelensky (@Hohlick)

To be honest, I’ve never used this menu to change type. Much faster was to change it from ribbon and from new small button in column header, and then manually add locale.

Good to know that at least option exists, although is very hidden :-(.

Sort months by first letter in PowerPivot and Power BI

Ivan Bondarenko October 10, 2016 2 Comments

This post is a logical continuation of previous one where I described how to place measures in rows in Power BI table visual.

Here I again talk about trend tables that looks like

It displays several measures in a matrix with months, so we may see development of each measure.

Data viz gurus will say that such data must be on chart.

Right, but sometimes people want to see figures in tables and … nothing else.

One of the problems here is to display month first letters in right order. (more…)

How to organize measures in tables horizontally in Power BI

Ivan Bondarenko October 7, 2016 9 Comments

Standard way of display measures in Power BI table visuals (Table and Matrix) – in columns.

But what if you required to display measures in rows and Year/Month in columns to see trend and remain relation with slicers and other visuals, keeping table flexible?

I couldn’t find something like Transpose table in Table/Matrix properties. And it is not possible to pull measure in rows.

In addition, required table contains rows with same value in column [KPI] – “Growth” with indent. And in different rows it has Growth of different measures.

How to do this in Power BI Desktop? (more…)

Part 2: Combination of rows of tables list in Power Query

Ivan Bondarenko September 22, 2016 1 Comment

Part 1 describes approach showing how to work with List.Generate, Table.FromRecords, List CrossJoin in Get & Transform (aka Power Query, M language).

Homework is done. Advice is digested.

And I’m ready to introduce another solution to combine multiple tables rows.

It is so simple, and can be done in UI!

All regards and honor should go to Imke Feldman (http://www.thebiccountant.com/) as she found that “epic Pokemon”.

I looked at this task as a programmer, thinking about loops and iterations.

Imke – completely differently – as simple user, don’t even thinking about programming, as she said :-).

Remind, we start from four tables

And want to mix all rows to get all possible combinations (indexed).

How to do this using UI only? (more…)

Big cake for BI-day

Ivan Bondarenko September 21, 2016 1 Comment

No, it is not another birthday of PowerBI :-). It is me turned 31 today.

Every year my wife bakes a cake for my colleagues on my birthday.

And every year “birthday, b-day, bi-day, BondarenkoIvan-day” cake for colleagues is decorated by something related to the most interesting moment in my work during last year.

2013 I joined HILTI Russia (HILTI-case), my first experience of work on global scale, a place where I significantly developed myself, started blogging to improve English, met a lot of smart and friendly people, where many things happened with me, long list actually.

2014 we finished six implementations of SAP Business ByDesign, where I was responsible for reporting and workarounds for processes, that system doesn’t handle (cake with flags).

2015 I won HILTI Star Club reward as “best entrepreneur”, optimizing business processes, pro-actively helping everyone with automation of routine, reports, Excel related stuff, simply consulting on best practices, training colleagues and so on (2-floor cake decorated with stars). Deluxe trip to Cancun paid by company was absolutely awesome, unforgettable!

2016 we finally started an official project on PowerBI. Very significant moment.

If you have some time to explore other sweet works – welcome to my wife’s collection of cakes.

Combination of rows of tables list in Power Query

Ivan Bondarenko September 20, 2016 6 Comments

Recently I faced interesting Power Query problem. Actually, initial problem has nothing related to Power Query.

I required to export quite big volume of data from SAP BW using Business Objects Analysis addin (BO Analysis, or BOA) and save as CSV. Amount of data is literally huge, so it was impossible to export it in one query and even in 10.

I wrote a VBA macro with a loop, where each iteration had to 1) set variables and filters 2) refresh data 3) save result as csv.

So, I had to prepare set of variables and filters for each step of loop, setting filters on several dimensions.

Following four tables describe all possible filters I needed:

(more…)

Unfold Child-Parent hierarchy in Power Query

Ivan Bondarenko September 9, 2016 3 Comments

In this post I consider two types of hierarchies

First table defines typical hierarchy of companies.

Usually, such hierarchy is used for financial reports to group key figures.

Most probably, we know number of hierarchy levels upfront, but not always.

Task: Expand hierarchy, automatically detect quantity of levels in resulting table using Power Query (Get & Transform) / Power BI

Stay close to scenario described in pattern: http://www.daxpatterns.com/parent-child-hierarchies/

Goal:

Second table defines substitution of products.

Case: sometimes we required to get sales history of all predecessor products and group result on Actual Product. There can be multiple substitutions, we never know amount.

Task: In this case we need to find the latest successor, Actual Product for each product in other words.

Goal:

(more…)

Refresh Excel Data Models on schedule

Ivan Bondarenko August 30, 2016 3 Comments

UPD: Excel-based version of Power Refresh (Reports Controller) is here.

I know what you think.

Yet another solution to refresh Excel files with connections and Data Model (aka PowerPivot).

Plenty of them can be found on the Internet. Different programming languages, different level of complexity

C#:

https://github.com/alapolloni/ExcelRefresh
Excel and PowerPivot refresh suite: https://excelrefresher.codeplex.com/

Perl: http://search.cpan.org/~ctbrown/Win32-Excel-Refresh-0.02/Refresh.pm

Power Shell: https://rpmachado.wordpress.com/2015/07/10/powershell-auto-refresh-excel-powerpivot-data/

VBA: http://velin-georgiev-blog.appspot.com/blog/fully-automated-excel-reporting-by-using-windows-task-scheduler-vba-and-simple-vba-functions-library/

And, of course – Power Update

http://exceleratorbi.com.au/power-update-software/

http://www.powerpivotpro.com/2015/02/introducing-power-update/

Year ago, when I decided to write my own refreshing program, Power Update was already on market, however, I couldn’t get it for myself, due to some reasons. In addition, I wanted to slightly adjust a process of refreshing and saving.

(more…)