Adding Header on Excel Sheet Using M Code

Hi Anonymous,
Thank you for reaching out to the Microsoft Fabric Community forum.

 

We have taken sample data and created parameters for Start_Date, End_Date, Reporting_Month as follow:

 

Then Open the Advanced Editor and write the M code to apply the logic:

 

    // Access Parameters

    Start_Date = #date(2022, 1, 1), // Replace with parameter value if using dynamic inputs

    End_Date = #date(2022, 3, 31),   // Replace with parameter value

    Reporting_Month = #date(2022, 2, 28),  // Replace with parameter value

 

    // Validate Parameters: Ensure End_Date is within 3 months after Start_Date

    Validated_End_Date =

        if End_Date <= Date.AddMonths(Start_Date, 3) then

            End_Date

        else

            error "End_Date must be within 3 months after Start_Date",

 

    // Validate Reporting_Month

    Validated_Reporting_Month =

        if Reporting_Month <= Date.AddMonths(Start_Date, 3) then

            Reporting_Month

        else

            error "Reporting_Month must be within 3 months after Start_Date",

 

    // Load Data from gbkmut Table

    Source = Excel.CurrentWorkbook(){[Name="gbkmut"]}[Content],

   

    // Ensure 'Date' Column is of Type Date

    ChangedType = Table.TransformColumnTypes(Source, {{"Date", type date}}),

 

    // Filter Data Based on Start_Date and Validated_End_Date

    FilteredData = Table.SelectRows(ChangedType, each [Date] >= Start_Date and [Date] <= Validated_End_Date),

 

    // Add Division Column

    FinalTable = Table.AddColumn(FilteredData, "Division", each "XXX"),

 

    // Return Final Table

    Result = FinalTable

in

    Result

 

Next close and load the data and click Refresh All to refresh the data.

 

Follow these steps to create a Power Query solution that dynamically filters data based on user input and adds a division column. You can update the parameters and refresh the data anytime for new results.

For your convenience, I’ve attached the Excel file  with the implemented solution. Please review it and let us know if there are any additional adjustments needed.

 

If this post clears your doubt, please give us Kudos and consider marking Accepting it as a solution to guide other members in finding it more easily.

 

Best regards,
Sahasra.