Power Query

Today I am excited to share a second excerpt from the draft manuscript of my book. Today’s excerpt is the first part of chapter 11, which discusses basic text analytics, and a cool technique to detect multiple keywords in textual feeds using Cartesian Product. To read more excerpts, as they will be published on the …

How many times did you have to work with tables that had headers like these? Client_ID, Transaction_ID, Product_Name, My_DBA_is_in_Love_with_Underscored_Headers No need to manually rename the column names any more, if you work with Excel Get Data / Power Query, or Power BI. You can just apply the following formula in Query Editor: [crayon-5a85df266ff17603321547/] Let’s explain. …

The good documentation of custom M functions is a key factor for efficiency in Power Query and Power BI Desktop. In my first post on this topic, I explained why documentation is important and how to add those to custom functions by changing metadata. If you haven’t read the previous article, you should do so […]

This is a cool example of how to Extract Data Based on the Previous Row, which came up as a viewer's question inside our Power Query Academy. Let's look at how we solved it… What Kind of Data Needs This … Continue reading →

instead of splitting each column, I Split each row using function Text.Split and added it as a new column. Text.Split returns a list. Later,

I did it in one table instead of creating new table for each character's id. We need one table and one function. Here's full solution: 1. Create function getCharacter(id): let Source = (id as text) => let Source = Json.Document(Web.Contents("https://swapi.co/api/people/" & id))...

One of the challenges I had working with a client recently was around how to work with ranges/bands of numbers in Power BI so thought I’d share the approach I took to this. I was looking to deal with …

I found this and it works: let Source = Sql.Database("DB_SERVER", "DB_NAME", [Query="exec STORED_PROCEDURE_NAME'"&Date.ToText(Date.From(DateFrom), "yyyy-MM-dd")&"', '"&Date.ToText(Date.From(DateTo), "yyyy-MM-dd")&"' , 62", HierarchicalNavigation=true]) in Source And it works! Thank you guys

I recently had a requirement from a customer who needed to filter reports based on global option sets. The kicker for this requirement was t...

It may not be immediately obvious, but you cannot set your own connection string properties when connecting to SQL Server using the built-in SQL Server connector from either Power BI or a modern da…

The nice people at PASS have made a video of my session on “Using SSAS MD as a data source for Power BI” available to view for free on YouTube: I’m honoured that it’s listed…

I was playing around with a scenario this morning where I was adding try results together in order to count how many columns were filled with information. What I needed to do kind of surprised me a little bit.

Selecting items from lists is often used for cleaning text-fields. List.SelectPositions allows you to select them by addressing their positions in Power Que

Table.Group in Power BI and Power Query has a very powerful 5th parameter that's worth checking out. Case insensitive grouping is just one cool feature.

Visual Studio 2017 and the SQL Server Data Tool (SSDT), a tool that comes with Visual Studio, finally integrates the now familiar Query Editor (Power Query) capability to work with the Analysis Ser…

This post shows how to write documentations for custom functions in M for Power Query for Excel and Power BI Desktop, by modifying metadata.

Let's take a deep dive into understanding how the each keyword in Power Query, which is used by quite a few of the built-in operations (filtering, custom columns, etc.).

My new function Text.RemoveRepeatingCharacters cleans up badly formatted text where spaces or other separators are applied multiple times.

Hello P3 World! My name is Krissy Dyess and I have had the most amazing experience working as a full-time consultant at Power Pivot Pro since April 17, 201

The Query Editor in Power BI/Excel Get&Transform/Power Query has a number of built-in ways to filter data in date columns relative to the current date, such as the “In the Previous” option. How…

Hello Everyone! In this episode of the “Problem, Design, Solution” series we will be talking about the “Folder” option inside the sources display for Power BI Desktop. This is a fantastic feature …

A new, as-yet undocumented, M function appeared in the December 2017 release of Power BI Desktop (I assume it will appear in Excel soon): Text.Select. Here’s the documentation from the Query Editor…

I am excited to share with you an excerpt from the draft manuscript of my book. You can also add it to your list in Amazon here. I expect to publish more excerpts in the coming months, so please stay tuned, and subscribe to DataChant. Appending Worksheets from a Workbook Excerpted from the draft manuscript …

One of my favourite features in Excel and Power BI is Power Query / M – and I just wanted to share a small trick when you want to filter a table on specific items. Let’s imagine you want to f…

If you want to quickly create a list with all dates (or specific intervals) between 2 dates, this function is for you: Date.DatesBetween