How to transform wide-format data into the structure shown in 'Result' sheet using Power Query?

One more variant. First, to query the source data we need to transform it to structured table, or define the name, or query from separate file. I'd use second variant. Since data is expandable, I'd define the name with some gap, for example

We query this named range and as first step remove empty rows and columns. Next, from first two rows we combine headers for our table. Skip these two rows and apply headers to the table.

Next, unpivot other but first 3 columns of the table splitting the Attribute column on Date and Measure.

We don't hardcode any field names within the query, with we flexible as with number of fields as with their names.

Entire query is

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

    RemoveEmptyColumns = Table.SelectColumns(
        Source,
        List.Select(
            Table.ColumnNames(Source),
            each List.NonNullCount(Table.Column(Source,_)) <> 0
        )
    ),
    RemoveEmptyRows = Table.SelectRows(
        RemoveEmptyColumns,
        each not List.IsEmpty(List.RemoveMatchingItems( Record.FieldValues(_), {"", null} ) )
    ),

    // prepare and set headers
    one = Record.FieldValues( RemoveEmptyRows{0} ),
    two = Record.FieldValues( RemoveEmptyRows{1} ),
    headers = List.Transform( {0..List.Count(two)-1},
        (q) => Text.Combine( {DateTime.ToText(one{q}, [Format="yyyy-MM-dd"] ), two{q} }, "=" ) ),
    RemoveTopRows = Table.Skip(RemoveEmptyRows,2),
    SetHeaders = Table.RenameColumns( RemoveTopRows, List.Zip( {Table.ColumnNames(RemoveTopRows), headers})  ),

    UnpivotColumnsButFirstThree = Table.UnpivotOtherColumns(SetHeaders,
        List.FirstN( two, 3 ), "Attribute", "Value"),

    SplitAttribute = Table.SplitColumn(UnpivotColumnsButFirstThree, "Attribute",
        Splitter.SplitTextByDelimiter("=", QuoteStyle.Csv), {"Date", "Measure"}),
    DeclareType = Table.TransformColumnTypes(SplitAttribute,{{"Date", type date}})
in
    DeclareType

I'd don't sort result within query. First, that's expensive operation. Second, we'll be more flexible if apply sorting to the resulting table directly

Please note, since you generate random numbers in the sample, we can't compare result with the source. Query takes data, after loading the result into the grid RANDBETWEEN generates another set of numbers in the source.

In the attached file you can add data to the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.

Try the following:

let
    // Load the source table
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],

    // Unpivot all date-measure columns
    Unpivoted = Table.UnpivotOtherColumns(Source, {"Product Brand", "Product Name", "Region"}, "Attribute", "Value"),

    // Split the 'Attribute' column into Date and Measure
    SplitColumn = Table.SplitColumn(Unpivoted, "Attribute", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"DateText", "Measure"}),

    // Convert the date string to actual date format (assuming dd.MM)
    AddedYear = Table.AddColumn(SplitColumn, "Date", each Date.FromText("2025-" & Text.Middle([DateText], 3, 2) & "-" & Text.Start([DateText], 2)), type date),

    // Remove the original DateText column
    RemovedDateText = Table.RemoveColumns(AddedYear, {"DateText"}),

    // Reorder columns for clarity
    Reordered = Table.ReorderColumns(RemovedDateText, {"Product Brand", "Product Name", "Region", "Date", "Measure", "Value"})
in
    Reordered