Pivoting a table and altering column names

[deannapi]

I'm importing an excel sheet that needs some clean up.  My data looks like the following:

	July 2023	August 2023	September 2023
VenueNameA
Total
Revenue	123456.23	456893.55	9843521.22
Gross Revenue	136031.22	654231.55	654621.23
Tax	465.5	545.55	89452.22
Retail
Revenue	123456.23	456893.55	9843521.22
Gross Revenue	136031.22	654231.55	654621.23
Tax	465.5	545.55	89452.22
Online
Revenue	123456.23	456893.55	9843521.22
Gross Revenue	136031.22	654231.55	654621.23
Tax	465.5	545.55	89452.22
VenueNameBB
repeat......

ColumnA is the column that needs clean-up

• Contains a VenueName
• Contains types of $ - Total, Retail, Online
• Then the breakout types of $ which are all the same
  • preferred would be to have type and $ in one column and then VenueName in another

Total Revenue	VenueA
Total Gross Revenue	VenueA
Online Revenue	VenueA
Online Gross Revenue	VenueA

or to have 2 columns of Type and then $ subtype

Total	Revenue	VenueA
Total	Gross Revenue	VenueA
Online	Revenue	VenueA
Online	Gross Revenue	VenueA

I know that after I clean up ColumnA, I can "unpivot" the Month-Year columns - But I'm not sure how to go about the cleanup.

I thought about somehow using index in a custom column, but again not sure how to create that. 

[spinfuzer]

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"

[speedramps]

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.