power query

A few days ago, I read an interesting blog post of Lukas Lötters [b|t] where he is explaining how to use regular expressions in Power BI (M language) by running an R script. It’s...

Callum - Callum Green's Blog -

Select Table 2, click the Query tab, and click Reference. This will create a new query that references Table 2 and will allow us to add a lookup column without enabling Fast Combine.Add a custom column with the following formula: List.First(List.Select(Table1[Product],(productName) = Text.Contains(productName, [Product])))Viola!

I answered some forum questions recently that were similar in nature and I thought they would make a good blog article. The scenario is this: there is a ta

Year ago I wrote a post Transform table column using own function in Power Query. According to stats, it is quite popular topic, and I’m not surprised. However, I think that old post is quite…

Power Query’s Text.Trim function doesn’t clean whitespace inside the text string, only at the ends. This article shows a custom function that does.

let Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content], Subtotals = Table.Group(Source, {"Category"}, {{"Category Sales", each List.Sum([Sales]), type number}}), JoinTables = Table.Join(Source, "Category", Subtotals, "Category"), InsertedCustom = Table.AddColumn(JoinTables, "Share", each [Sales]/[Category Sales]), RemovedColumns = Table.RemoveColumns(InsertedCustom,{"Category Sales"})in RemovedColumns

Follow Me In today’s tutorial we will solve a very common challenge in Excel. You start with an Excel table and you wish to condense it by removing all the columns and/or rows which are entirely empty, and usually you don’t have prior knowledge which columns and rows are empty. For example, In the screenshot …

Quite often, in Power Query, you want to

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

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…

Recently I had a request for help from someone who wanted to do the following in Power Query: take a piece of text and then, using a table, search for all of the occurrences of the words in one col…

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