Code Generator | M (Power Query)

Table Date Filter

This M code snippet filters the records in the `SourceTable` based on a `date` column.


Empty image or helper icon

Prompt

Could you generate an M code to filter records for the last 7 days based on a 'date' column in Power Query?

Answer

let
    // Get the current date
    currentDate = DateTime.LocalNow(),

    // Calculate the date 7 days ago
    startDate = Date.AddDays(currentDate, -7),

    // Filter the table based on the date column
    filteredTable = Table.SelectRows(SourceTable, each [date] >= startDate)

in
    filteredTable

This M code snippet filters the records in the SourceTable based on a date column. It calculates the current date using DateTime.LocalNow() function and then subtracts 7 days using Date.AddDays() function to get the start date for the filter. Finally, it uses Table.SelectRows() function to filter the SourceTable based on the date column, keeping only the records that are greater than or equal to the start date. The resulting filtered table is returned as the output.

Create your Thread using our flexible tools, share it with friends and colleagues.

Your current query will become the main foundation for the thread, which you can expand with other tools presented on our platform. We will help you choose tools so that your thread is structured and logically built.

Description

The code snippet demonstrates how to use M language functions to filter a table based on a date column. It first retrieves the current date using the DateTime.LocalNow() function. Then, it calculates the start date by subtracting 7 days from the current date using the Date.AddDays() function. Finally, it applies the filter using the Table.SelectRows() function, keeping only the records where the date is greater than or equal to the start date. The resulting filtered table is returned as the output. This code snippet can be used in Power Query, Power BI, or other tools that support the M language for data transformation.

More Code Generators
Apache Flink Code Generator Apache Flink Code GeneratorApache Pig Code Generator Apache Pig Code GeneratorAzure Data Factory Code Generator Azure Data Factory Code GeneratorC/C++ Code Generator C/C++ Code GeneratorCouchDB Code Generator CouchDB Code GeneratorDAX Code Generator DAX Code GeneratorExcel Code Generator Excel Code GeneratorFirebase Code Generator Firebase Code GeneratorGoogle BigQuery Code Generator Google BigQuery Code GeneratorGoogle Sheets Code Generator Google Sheets Code GeneratorGraphQL Code Generator GraphQL Code GeneratorHive Code Generator Hive Code GeneratorJava Code Generator Java Code GeneratorJavaScript Code Generator JavaScript Code GeneratorJulia Code Generator Julia Code GeneratorLua Code Generator Lua Code GeneratorM (Power Query) Code Generator M (Power Query) Code GeneratorMATLAB Code Generator MATLAB Code GeneratorMongoDB Code Generator MongoDB Code GeneratorOracle Code Generator Oracle Code GeneratorPostgreSQL Code Generator PostgreSQL Code GeneratorPower BI Code Generator Power BI Code GeneratorPython Code Generator Python Code GeneratorR Code Generator R Code GeneratorRedis Code Generator Redis Code GeneratorRegex Code Generator Regex Code GeneratorRuby Code Generator Ruby Code GeneratorSAS Code Generator SAS Code GeneratorScala Code Generator Scala Code GeneratorShell Code Generator Shell Code GeneratorSPSS Code Generator SPSS Code GeneratorSQL Code Generator SQL Code GeneratorSQLite Code Generator SQLite Code GeneratorStata Code Generator Stata Code GeneratorTableau Code Generator Tableau Code GeneratorVBA Code Generator VBA Code Generator