Adding Header on Excel Sheet Using M Code

[tksnota]

Hi

I had a query completed and working but I was requested to put a header on top of the query  that will allow the user to only show data based from the timeframe they want after the excel sheet has been refreshed.

All of the data will be taken from the same table called gbkmut and fields will be as follows

Division: XXX
Start_Date:  01.01.22 (user can specify prior day, month and year)
End_Date: 31.12.24 (user can specify future day, month and year. If he/she wants prior timeframe then there should be a limit of 3 months after the Start_Date)
Reporting_Month: 31.10.24 (user can specify future day, month and year. If he/she wants prior timeframe then there should be a limit of 3 months after the Start_Date)

thanks...tksnota..

[tksnota]

Thanks for this code...Is it ok for me to share clip so the forum can understand what's needed? If so, please share the link to share the short clip.

[v-sgandrathi]

Hi @tksnota,

We apologize for any inconvenience, but we are unable to share the link.
For your reference, I have already shared the .xlsx file. If you continue to face issues, please provide a screenshot for more precise assistance.

Thank you for being a part of the Microsoft Fabric Community.

[v-sgandrathi · Accepted solution]

Hi @tksnota,
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.

[Greg_Deckler]

@tksnota You would use a query parameter for that.