power query

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.

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…

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…

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...

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

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

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,

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 →

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 […]

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

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 …

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…

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

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

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

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.

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

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 …

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 …

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

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?

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

CSV files are one of the most commonly used data sources in Power BI and Power Query/Get&Transform, and yet the documentation for the Csv.Document M function is very limited and in some cases i…

I worked through a brain-teaser on a consulting project today that I thought I’d share in case it was useful for someone else in the community. We needed to convert application user names into an …