The R ways in #PowerQuery. How R.Execute() can be used for other non-statistical related tasks #PowerBI

Get your own Power Query editor with intellisense, snythax highlighting, parameter hints and help texts. Custom M code has never been easier to write.

I saw a blog post recently where Jay Killeen was talking about how he handles his Source Control for the Query Editor or Power Query. And this got me thinking of how I could automate this in a quic…

Sometimes (dare I say often, or even usually) the data you have available is not in the ideal shape for analysis in Power BI. These problems can manifest i

Here comes some long awaited comfort functions for part 2 of my easy P&L series, where you don't have to define the single accounts any more.

Today I discovered that we can use conditions in many of the N-selecting functions where one/I would normally expect just a number-expression for the N: Table.RemoveFirstN( table as table, optional countOrCondition as any) (…) Read more

The other day when I was thinking about Power Query functions (as you do) it occurred to me that they would be a lot more usable if, when you invoked them, you could show your users a dropdown box …

Things to keep in mind when implementing time-delayed evaluations in #PowerBI #PowerQuery

Follow Me Order is a crucial force in life, especially if you are a data analyst. But as we will see today, the misuse of order might cause an unexpected chaos. In today’s post, we will discuss the chaotic nature of column ordering in the Query Editor of Power BI and Power Query / Get & …

In a previous blog post, I explained how to list all the Data Platform MVP by parsing a single web page. In a second blog post, I parsed the individual pages of each MVP to get information about he…

In a first blog post, I explained how to list all the Data Platform MVP by parsing a single web page. In a second blog post, I parsed the individual pages of each MVP to get information about her a…

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