What if part of your reporting database is a set of CSV files?
Apart from possible problems with different number of columns, data types, delimiters, encoding etc., you have to care about performance.
According to my practice, large number of files kills productivity. It is better to firstly combine CSV / TXT files into one, then use Power Query to load it.
Developing queries for Power BI and Power Query I had to look into documentation or check my previous solutions from time to time in order to get answers to same questions again and again. So, I ended up with a creation of cheat sheet for myself. Couldn’t wait more for cheat sheet from Gil Raviv (know, he plans to make it, stay tuned).
My version is not nicely formatted as DAX Reference Card from PowerPivotPro, but still helpful. It helps me with rarely used symbols and data types, and vice versa, frequently used pieces of M code. E.g. Carriage Return symbol in Power Query, get Excel cell value, or work with datetime and duration types etc.
In my previous post I wrote about one interesting technique used by my colleague Zoltán Kaszaki-Krsjak.
Categoty_tmp = Table.AddColumn(Buffer, "Category_tmp", each Buffer[Category]{[ID2]}?),
// It helps to shift values of column [Category] one row upwards.
It is a trick that you most probably will not use in any of your solutions. However, worth to know how it works and how to do this without adding new columns.
“each Buffer[Category]{[ID2]}?” is just a function, so we can combine it with methods described in one of my posts (Transform Column Using Custom Function).
Here is the code.
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Duplicated Column" = Table.DuplicateColumn(Source, "Category", "Method1"),
#"Duplicated Column1" = Table.DuplicateColumn(#"Duplicated Column", "Category", "Method2"),
#"Duplicated Column2" = Table.DuplicateColumn(#"Duplicated Column1", "Category", "Method3"),
Buffer = Table.Buffer( #"Duplicated Column2" ),
Method1 = Table.FromRecords( Table.TransformRows( Buffer, each [Index = [Index],
Category = [Category],
Method1 = Buffer[Method1]{[Index]}?,
Method2 = [Method2],
Method3 = [Method3] ] ) ),
Method2 = Table.FromRecords( Table.TransformRows( Method1, (row) =>
Record.TransformFields( row,
{"Method2", each Buffer[Method2]{ row[Index] }? } ) ) ),
// method offered by Miguel Escobar in comment to previous post
Method3 = Table.ReplaceValue(Method2, each [Method3], each Buffer[Method3]{[Index]}?, Replacer.ReplaceValue, {"Method3"})
in
Method3
File with methods is here.
But which method is faster?
Seems, Excel charts is an area that till now wasn’t considered in blogs as a target for Power Query application (Get & Transform in Excel 2016).
Nevertheless, PQ can replace some VBA solutions and make your workbooks macro-free.
In far 2015 my colleague Zoltán Kaszaki-Krsjak shared with me a very good example of how Power Query can help with generation of specific tables for specific charts, which are widely used in our organization.
Idea to write a blog post about this technique became dusty in me OneNote, and probably would wait more if only Jon Peltier hadn’t attracted my attention to this topic again by his recent post.
Sample workbook contains a solution for Bar-Mekko chart (or “variable width column chart”)
Such chart allows to easily see share of categories, growth or absolute value. Can be used to compare market segments or productivity of departments / subsidiaries. Red line in this case shows average growth – another small but important detail.
Interested how to build it?
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
Let’s do this.
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…
Производственный календарь (или календарь рабочих и праздничных дней) – одна из важных составляющих многих отчетов и инструментов планирования.
Из-за “плавающих” выходных и праздничных дней в России мы не можем каждый год использовать один и тот же набор данных.
Лично у меня ежегодно возникает вопрос – где скачать производственный календарь. Желательно в удобном формате Excel и с наименьшими трудозатратами. И вот совсем недавно я нашёл для себя решение, которое, уверен, пригодится многим.
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
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
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 :-(.
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…)
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:
