power query

PowerBI doesn't have a connector for Elasticsearch, but by using the Web connector with the Elasticsearch REST API you can easily combine the two

Inspired by Ruth Pozuelo from Curbal latest video - https://www.youtube.com/watch?v=jlogBrcYZwc&t=2s where she shows an excellent tip on how to change column headers to upper or proper case – And the …

See, how the new M editor in Power BI Desktop helps you to write M code easily, with Intellisense, Keyword highlighting, parameter hints and help texts.

Power Query, an Excel add-in from the Power BI family, allows you to extract and manipulate data from various sources. When you define transformations on the data, it is possible that those transformations are sent back to the source, in order to improve

Time to finish up our exploration of primitive, single-value holding types! We’ll learn about handling true and false, explore the oxymoron of null (a value that represents the absence of a value) and…

I was looking at the list of M functions supported in custom connectors and not in Power BI Desktop (using the technique I blogged about here) in the latest version of the Power Query SDK when I came …

In the Microsoft Flow October 2018 update - link – we got a very interesting feature added to Microsoft Flow – Power Query and as they state – Also, to better empower our users – we have integrated Po…

We have been working on a set of helpful Power Query Recipes for Excel and Power BI and are excited to announce that they are now officially available!

I was super-excited when, a few days ago, the long-awaited integration of Power Query into Microsoft Flow was announced. I was then gutted when work commitments meant I couldn’t blog about it right…

When you work with tables and queries in Power Query and Power BI, you get the option to copy them through these actions: Duplicate, or Reference. It has been always a question in my sessions and cour…

This is a very short post! A lot of people have blogged about how to convert numbers between different bases in M (see for example Maxim Zelensky’s very elegant solution for converting from binary …

It is straightforward to compare values between each row in an Excel table and the next row. You can create a calculated column in a table and reference values in other columns in the same row by name and cells in different rows by using regular referencing. Such a reference is fragile. Sorting or deleting rows will

This is going to sound obscure, and it is, but recently I’ve been using the #table() intrinsic function in M a lot – specifically the version that takes a table type as its first parameter (as I descr…

There are two kinds of nuthin' in Power Query: null and blank. Why is this important? Because null and blank are not equal!

The short answer: no. The long answer: It is, technically, possible to use Power Query (in either Power BI or Excel) to write data to a data source; for example, here’s an old post I wrote showing …

This is exciting! The digital version of the book “Collect, Combine and Transform Data Using Power Query in Excel and Power BI” is out. You can purchase it on Amazon here or on Microsoft Press Store here. Note: The book includes 70+ step-by-step exercises with many sample files that are key components of your learning experience …

As a heavy R and Tidyverse user, I’ve been playing with Microsofts m/Power Query language included in Excel and PowerBI from that perspective, looking for the functions to make my life easier, developing small code pipelines for my processing and trying to get a smooth, clear and maintainable data manipulation process in place. The Problem In PowerBI I have data generated from an API call to HubSpot, which deliveres a json which is flattened as the first step of the process into a table with hundreds of columns.

Certain M functions accept a Culture parameter that ensures the result is returned in the defined language, regardless of the language of the OS.

Recently I picked up an interesting request to transform the words in a column to proper case, but keep certain keywords like in a defined list. So let's transform text with exceptions:

You know it and I know it. Data quality is a BIG problem that reduces the business value of BI. ETL practitioners will probably recall that SSIS includes a comprehensive Data Profiling Task but it is …

Today Microsoft released the October 2018 update of Power BI Desktop. One of the fabulous new features that has been released is the ability to get a visual profile of your data in Power Query BEF…

We are back to finish our discussion on the visualizations for Comparing Rows in Power Query! How to visualize the prices and daily changes The two metals have very different price ranges so it is difficult to see them in one chart. The daily changes are relative so they can be used to compare the price behavior

Power Query is amazing for cleaning, shaping and analyzing numerical data, buut it can also help with analyzing text that is unstructured.

A quick update: The print version of the book will be released in 4 days! It may take few extra days to reach the major distribution centers. On Amazon there are some data sync glitches right now, so …

For a while now I’ve had an idea stuck in my head: wouldn’t it be cool to build a Power BI solution where a user could enter data into an Excel workbook and then, as soon as they had done so, they …