Adding allowed values to your custom M function adds high value to the user experience. This post shows you how to do that in the M language for Power Query and Power BI Desktop

In a comment on a previous post, a reader asked how you return a specific day of the next month from any given date. In other words, I've got a date of March 5 and I want to use Power Query to return April 10 in Excel (or Power BI). How do you do it?

When you are working with data in Excel or PowerBI the data often contains columns that is a combination of text and numbers. One example could be like this If you have this challenge you shouldn&…

Have you ever wanted to find out how many days you have between 2 dates? perhaps how many Mondays? how many Sundays? perhaps Saturdays and Sundays? Well, in this blog post I’ll show you how you can do that with Power Query inside of Excel and/or Power BI and how you can extend this to …

When you’re working with M code in Power BI or Power Query/Get&Transform you may want to include special characters such as carriage returns, line feeds or tabs inside a text value. To do this …

Imagine this scenario, you are a Doctor who has multiple appointments with patients on a daily basis. Sometimes, you need to schedule follow up appointments with your patients just to make sure that everything is going according to plan and basically do a check up. You record these appointments in a simple spreadsheet like the …

The scenario – source data sheets that are structured differently! Does Power Query sometimes seem too rigid? It’s great for defining a sequence of transfo

Create a fully dynamic Power BI table. This allows a user to select the Top N items from any number of column data fields. This table technique allows for wide flexibility in what your users are able to investigate within one table.

Two different options to combine multiple tables into one: 1) easy and obvious 2) works for dynamic selections and large number of tables

Although it's not much code, it's so convenient to have a generic equivalent to the DAX-SWITCH-function in M for the query editor in Power BI or Power Query

From the Water Cooler As a 'water cooler' of sorts for this community, we meet some amazing people. Doug Burke shares with us how easy Power Query makes it

Writing M in the Advanced Editor in Excel or Power BI can be a frustrating experience unless you’re the kind of masochist who loves writing code in Notepad. There are some options for writing M cod…

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.