Pivoting a table and altering column names

Try this in Power Query ....

Import your raw data

Insert a Venue column

Replace blabk Vneues with null

Use "Fill down" to populate the Venue columns

Remove the unneeded Venue rows, because now you have Venue column 

Select just the first and last column and the unpivot other columns

Change the period column to to date data type

Thanks for the clear description of the problem with example data. I wish everyone did that!

This solution works and does EXACTLY what you asked.

Remember we are unpaid volunteers, and you have got free expert help which took a lot of effort,
So please quickly click the [accept as solution] and the thumbs up button to leave kudos. 

One question per ticket please. If you need to change or extend your request then please raise a new ticket.

You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you ! 

If you quote speedramps in your next tickets then I will then receive an automatic notification, and will be delighted to help you again.

Please now click the [accept as solution] and the thumbs up button.  Thank you.

1.  Make sure all the blanks are nulls if not already done.

2.  Make sure the dates are in the headers

3.  insert a new column "next_row"which is basically Column1 with the first value removed.

4.  Add custom column Type if [next_row] = "Revenue" then [Column1] else null

5.  Add custom column Venue if [next_row] = "Total" then [Column1] else null

6.  Fill down on both Type and Venue columns.

7.  Reorder columns

8.  Merge columns Type and Column1

copy and paste for a full example

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("7VLLCoMwEPwVyVmCbrJBj/ZS6KEFK72IhxSWIvgompT275soSqE/ILSHhdmdnWFgtywZC9nBNq8AIhAOZ/ZmR7N0Z7obaq80zIMqLNmFOktH3VLm+GAtTxW90c3XNKeHl7g+BiFR8cnZgSQVHNHhNJECIeYAk2A/9OMYfMiEisTEhkyhBIcnmcPKq+ZchX56W4XccyhxXkpSibA452R0vemEp66pO9pywvX+u93/AX7uAao3", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,{"Column1", "Column2", "Column3", "Column4"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Replaced Value", [PromoteAllScalars=true]),
    #"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", type text}, {"July 2023", type number}, {"August 2023", type number}, {"September 2023", type number}}),
    add_next_row = Table.FromColumns(
        Table.ToColumns(#"Changed Type2") & {List.Skip(#"Changed Type2"[Column1])},
        Table.ColumnNames(#"Changed Type2") & {"next_row"}
        ),
    #"Added Custom" = Table.AddColumn(add_next_row, "Type", each if [next_row] = "Revenue" then [Column1] else null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Venue", each if [next_row] = "Total" then [Column1] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom1",{"Type", "Venue"}),
    #"Filtered Rows1" = Table.SelectRows(#"Filled Down", each ([next_row] <> "Revenue" and [next_row] <> "Total")),
    #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows1",{"Type", "Column1", "Venue", "July 2023", "August 2023", "September 2023", "next_row"}),
    #"Merged Columns" = Table.CombineColumns(#"Reordered Columns",{"Type", "Column1"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    #"Removed Columns" = Table.RemoveColumns(#"Merged Columns",{"next_row"})
in
    #"Removed Columns"