Скачать производственный календарь в Excel

Производственный календарь (или календарь рабочих и праздничных дней) – одна из важных составляющих многих отчетов и инструментов планирования.

Из-за “плавающих” выходных и праздничных дней в России мы не можем каждый год использовать один и тот же набор данных.

Лично у меня ежегодно возникает вопрос – где скачать производственный календарь. Желательно в удобном формате Excel и с наименьшими трудозатратами. И вот совсем недавно я нашёл для себя решение, которое, уверен, пригодится многим.

(more…)

Changed Type step in Power Query

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)

change-type-right-click

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

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

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

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

powerbi cake

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.

powerbi cake

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

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

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…)

SAP ByDesign + Power BI = cloud friends


In this post you will find an idea how to pull data from SAP ByDesign directly to Power BI. This is a totally self-service solution, which can be built if you have literally nothing. Only you, Excel, free license of Power BI and SAP ByDesign tenant(s).

Samples based on data from test tenants that were opened for participants of openSAP course “Reporting with SAP Business ByDesign“.

Couple of words about SAP ByD for those who are not aware what is this. Cloud ERP system, has own real-time analytics engine, quite powerful, but poor. Poor calculation capabilities, poor visualization capabilities. Handles only basic things, obvious calculations.

Moreover, if you have several SAP ByDesign systems in responsibility you most probably have to consolidate data somewhere. Build sub-regional, regional reports etc.

(more…)

Power BI is generally available now

From the first glance Power BI is a dream as self-service BI if only cloud publishing platform is allowed in organization.

Set of short videos shows how easy is to work with Power BI
Power BI Desktop GA Update Overview – overview of new features of Power BI Desktop application.

How to create and use the Personal Gateway to keep your data up to date. – connect reports in cloud with on-prem data through own workstation.

How to create content packs for your organization to use in the Power BI service. – packs of reports, create, share with groups of colleagues.

How to connect a file from OneDrive to Power BI. – very useful scenario, when workbook with data stored on Onedrive.

I consider important to try “packs of reports” feature. It is easy when reports are done for one small company, where reports divided on two or three groups; but will it be easy to maintain such structure on Power BI for 10 countries with 5 areas of reports in each, where every group should be isolated?