Nested LET Statement in Power Query

If you have ever taken a peek into the Advanced Editor window of Power Query, you’d often see multiple lines of M Code. Some quick facts

1. Typically the code starts with a let and ends with an in statement
2. In between let and the in statements you will see intermediate steps taken to transform the data.

In this post I want to share the idea of writing a nested let statement, the reason

1. Each step in the let statement acts like a variable
2. You can use these variables (in a nested let) to make your query compact.

 

See this quick video..

Consider these dates

I have this data where Year, Month and Date have been concatenated and I want to create a proper date from this data.

 

Nested Let Statement in Power Query

Although you can do a bunch of steps to set this straight but writing our own let and in statements gives you far greater power to customize the query.

I have created a new custom column with the following M Code.

let
   Year = Number.From(Text.Start([Dates],4)),
   Month = Number.From(Text.Middle([Dates],4,2)),
   Date = Number.From(Text.End([Dates],2)),
   FinalDate = #date(Year,Month,Date)
in
   FinalDate

By creating this column I do get the correct date.

 

Did we really create a Nested let and in?

Let’s peek into the advanced editor and see the code. The custom column created a Nested let & in Power Query.

Although the code seems like it is running in multiple lines but there are actually just 2 steps in the applied steps window. Compact and Sweet!

 

 

A few More Power Query Tricks

1. Refer to Previous Row in Power Query
2. Expand All Columns Dynamically
3. Create a Dynamic File Path in Power Query
4. Change dates from MM-DD format to DD-MM format
5. Split by Variable Columns in Power Query
6. Replace Error Values in Multiple Columns