power query

Scenario: In Power BI Desktop, when we connect to a .json file, power query editor will transform it automatically if its structure is single. Sample data1: Output in power query: From the APPLIED STEPS we can find that power query editor expands all columns automatically to get this result table....

Scenario:     In this scenario, the column [City] in my table has some dirty data with some space and case errors. It's also a mixture of abbreviations and full names of the cities. I want to find a way to unify the value of the column ‘City’ into the abbreviation of the cities, and merge this abbr...

Enso – a visual and textual functional programming language. - enso-org/ide

The other day I was facing an interesting question "How can I randomly draw ten records from the top 50 for each group using Power Query?" As you may guess, this article also contains the answer.

In this post I will describe how to get previous row data that matches a criteria. This post will demo getting a person's previous score.

Increase the performance of Power Query transformations in Power BI Desktop by setting the MaxEvaluationWorkingSetInMB registry key

Last week I showed how the new MaxEvaluationWorkingSetInMB registry setting could increase the performance of memory-hungry Power Query queries in Power BI Desktop. In this post I’ll show how…

We are happy to announce that Office Scripts in Excel for the web is now generally available for all eligible users!   What is Office Scripts? Office Scripts is an automation feature-set in Excel for the web that allows users with all levels of programming experience to automate their repetitive wo...

If you have rows with blank values, you can use Remove Blank Rows or Remove empty to filter them out. learn about their differences here.

I had the pleasure speaking with Reza Rad on his YouTube live session. Watch out the recording and learn how to harness Power Query and dataflows as a low-code data quality solution and use my Dataflow Snapshots Analysis app.

Learn how to extract parts of a text column's value in Power BI using some very simple-to-use transformations in Power Query.

In this blog post, I'm discussing whether you should use Power Query in dataflows or in datasets, comparing the two options.

Learn how helpful can the add prefix and suffix in Power BI and Power Query can be. You will learn it through an example of generating URLs.

How the presence of nullable columns affects the way Power BI and Power Query generate SQL

An exponential moving average (EMA) is a type of moving average (MA) that gives a higher weight and importance on the latest data points. An exponentially weighted moving average responds more greatly to recent price changes than a simple moving average (SMA), which uses an equal weight to all obser...

There are different ways you can create buckets or groups for your data in Power BI. Patrick shows you how this can be done with Power Query along with a few other options.

If you’ve been following some of my recent posts about improving Power BI refresh performance by partitioning tables you will have seen a lot of screenshots that look like the one below: It&#…

The Web.Contents() function in M is the key to getting data from web pages and web services, and has a number of useful – but badly documented – options that make it easier to construct urls for yo…

Learn how to perform the Merge in Power BI and Power Query based on the BETWEEN operation. The sample showed using customized age bands.

Hi again. This is Maya (you can find me on Linkedin here), with my second post on DataChant: a revision of a previous tutorial. Removing empty rows or columns from tables is a very common challenge of data-cleaning. The tutorial in mention, which happens to be one of our most popular tutorials on DataChant, addressed […]

Learn how to perform fuzzy grouping and matching in Power BI and Power Query in a few steps described in this article and video

Dealing with estimated data often gives you numbers with more precision than you need or trust. Dynamic Rounding can create gains in performance.

I admit that the technique I will share with you in this post may not have many use-cases, but it was a crucial component for the Excel Compare Power BI app that we released on AppSource earlier this week, and you can consider reading this post as a technical challenge to evaluate your Power Query […]

Do you need to detect changes or errors between two revisions of mission-critical Excel workbooks? We have just released our 10th app on AppSource that can help you achieve it. Excel Compare (available on AppSource here) is a free Power BI app that compares two Excel files of your choosing and detects differences and errors […]

When working with Power Query, you have probably already realized that every expression you write returns a value of a specific type. Usually this will be a primitive type like text, number, or date. (A full list of types available in Power Query can be found here: https://docs.microsoft.com/en-us/powerquery-m/m-spec-types). If for some reason the type of…

Learn how to find similar values in the data table based on a similarity threshold and get the similarity score output with fuzzy clustering.