Hi bjzk. You're going to have issues if you use Table.ColumnNames(GetH123Prices) to derive the equivalent of ColumnList in my example. Instead, you'll need to specify the names of the columns you want to check against (or use a formula to automatically whittle down the list of column names to exclude H0 and any other columns you don't want to check). For example:= Table.AddColumn(GetH123Prices, "ContainsH0", each List.Contains(Record.FieldValues(Record.RemoveFields(_, "H0_Price")), [H0_Price]))EhrenTuesday, August 09, 2016 6:26 PMReply | QuoteEhren - MSFTMicrosoft (MSFT) 6,290 PointsOwner

let ListOfWords = List.Buffer({"ed", "us", "an"}), Source = Table.FromColumns({{"red bus","blue train", "orange airplane"}}, {"some text"}), AddColumn = Table.AddColumn(Source, "Custom", (x) = List.AnyTrue(List.Transform(ListOfWords, each Text.Contains(x[some text], _))) )in AddColumn

let fxContain = (txt as text, idx as number) = let Result = if Text.Contains(txt,ListB{idx}) then ListB{idx} else if idx = ListCountB then null else @fxContain(txt,idx+1) in Result, ListA = {"help me rhonda", "in my room", "good vibrations", "god only knows"}, ListB = {"room", "me", "only", "help"}, ListCountB = List.Count(ListB)-1, ListC = List.Transform(ListA, each fxContain(_,0))in ListC

This article describes how you can store tables longer than 1,1 Mio rows in an Excel-sheet using Power Query and JSON compression.

Recently I was asked by a customer to do some tuning on an Excel workbook with a lot of Power Query queries in it. Although all of the data used in the queries was coming from tables in the workboo…

This article shows how to perform multiple replacements of text at once. This version is very fast. You will also learn some cool tricks in the M language.

If you’re a Power Query enthusiast you’re probably already comfortable with creating functions and passing values to them. However in some scenarios you don’t want to pass just a single value to a …

Post of Chris Webb inspired me on creation of alternative way to make replacement in table column using separate table with list of Old & New values. Say, more scalable solution. Because List.G…

More and more I’m seeing examples where people are trying to extract data from a mixed column.  In other words, they have two data types in a single column, but need to find a way to extract one from the … Continue reading →

About a week ago, without any warning, a much-awaited new feature lit up in Azure Data Catalog: the ability to share Power Query queries between workbooks and users. In fact it’s not really a new f…

Last week, Maxim Zelensky (whose blog is well worth checking out) tweeted about a very interesting answer he had received to a question he posted on the Power Query MSDN forum, on the subject of ca…

You can avoid long waiting time in your design process which is caused by data loading in the background by using filters. Centrally toggled will they speed

By Matt Allington The concept of parameter tables in Power Query will be familiar to some and new to others.  A parameter table is a way that you can “pass

When you’re writing an M expression for a custom column in Power BI or Power Query it’s easy to reference the values in other columns. A slightly more difficult question, however, is how can you re…

There are a lot of cool new features in the September 2016 update for Power BI, so many in fact that several of the really important changes in the Query Editor are in danger of going unnoticed. In…

in Power BI Desktop , PowerQuery Excel

One slightly frustrating feature of Power BI is that some of the cool stuff you can do in M code for loading data, and which works in Power BI Desktop (and in Power Query), causes errors when you t…

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…

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

In this post I consider two types of hierarchies First table defines typical hierarchy of companies. Usually, such hierarchy is used for financial reports to group key figures. Most probably, we kn…

The July 2016 update for Power BI Desktop included the ability to make parameters data-driven, by giving the option to bind the Suggested Values (previously called the Allowed Values) property of a…

There are many ways to get a value from parameters table and then pass it to query. One of this methods uses direct selection (quick filter) of unique parameter name.

Matt Masson and Theresa Palmer gave an excellent presentation on Power Query and M for the PASS DW/BI Virtual Chapter a few days ago (hopefully it will be on their YouTube channel soon). One thing …

A few weeks ago I wrote a post about comparing the contents of entire tables in Power Query, and a question came up in the comments about how you might go about comparing values in columns rather t…