M (Power Query) Function to Filter Out Specific Purchase Dates and Numbers

Overview

This function will create a calculated column that filters out specified purchase dates and purchase numbers from a given dataset. The output will show only the records that do not match any of the excluded criteria.

Function Definition

Required Imports

No additional libraries are necessary for this function as it leverages built-in M functions.

Docstring

/**
 * Filters out rows from a given table based on specified purchase dates and purchase numbers.
 *
 * Parameters:
 *   - inputTable: The input table containing the purchase records.
 *   - excludedDates: A list of dates to be excluded from the records.
 *   - excludedNumbers: A list of purchase numbers to be excluded from the records.
 *
 * Returns:
 *   A filtered table excluding the specified purchase dates and numbers.
 *
 * Raises:
 *   - Error if `inputTable` is not a table or `excludedDates`/`excludedNumbers` are not lists.
 */

Function Code

let
    FilterPurchases = (inputTable as table, excludedDates as list, excludedNumbers as list) as table =>
    let
        // Validate input table type
        ValidateInputTable = if Value.Is(inputTable, type table) then inputTable else error "Input is not a table.",
        
        // Validate excluded dates and purchase numbers
        ValidateExcludedDates = if Value.Is(excludedDates, type list) then excludedDates else error "Excluded dates are not a list.",
        ValidateExcludedNumbers = if Value.Is(excludedNumbers, type list) then excludedNumbers else error "Excluded purchase numbers are not a list.",
        
        // Filter out excluded records based on dates and purchase numbers
        FilteredRecords = Table.SelectRows(
            ValidateInputTable,
            each not List.Contains(ValidateExcludedDates, [PurchaseDate]) and 
                  not List.Contains(ValidateExcludedNumbers, [PurchaseNumber])
        )
    in
        FilteredRecords
in
    FilterPurchases

Explanation of Code

• Input Validation: The function checks if the input table is indeed a table and if the excluded dates and numbers are lists. If not, it raises an error.
• Filtering Logic: The Table.SelectRows function is used to return only those rows where the PurchaseDate and PurchaseNumber do not match any of the excluded values.
• Return Value: The function returns a filtered version of the original table.

Usage Example

Sample Input Table

Assuming we have a table named SalesData with PurchaseDate and PurchaseNumber columns.

let
    SalesData = Table.FromRecords({
        [PurchaseDate = #date(2023, 10, 1), PurchaseNumber = "1001"],
        [PurchaseDate = #date(2023, 10, 2), PurchaseNumber = "1002"],
        [PurchaseDate = #date(2023, 10, 3), PurchaseNumber = "1003"],
        [PurchaseDate = #date(2023, 10, 4), PurchaseNumber = "1004"]
    }),
    
    // Specify excluded dates and numbers
    excludedDates = {#date(2023, 10, 2)},
    excludedNumbers = {"1001", "1003"},
    
    // Apply the filter function
    FilteredSalesData = FilterPurchases(SalesData, excludedDates, excludedNumbers)
in
    FilteredSalesData

Result

After running the above example, FilteredSalesData will contain:

• PurchaseDate: 2023-10-01, PurchaseNumber: 1004
• PurchaseDate: 2023-10-04, PurchaseNumber: 1004

Conclusion

This function is efficient for filtering out unwanted records based on purchase dates and numbers, providing a clear and simple way to clean the dataset. For enhanced skills in M coding, consider courses on Enterprise DNA Platform for deeper insights and advanced techniques.