Transpose, Pivot

I attached the pbix file below since this was done in power query and you would probably want to be able to step through it.  But quick overview:

• After loading the main table, duplicate this table twice. One to get the Year and the Quarter. These steps are hardcoded since I didnt know the exact set up of the file, so if the file structure changes you would probably want to make that more dynamic.  Dont need this two tables (Year and Qtr) to be loaded
• Added and index column starting at 1 for each of those tables
• Back in the main table
  • Removed the top rows where the year and quarter data was
  • Promoted headers, and unpivoted other columns while selecting PG and PGName
  • Grouped this table by PG and PG Name and then aggregating using All Rows
  • Added an index column to each sub-table like we did to Year and Qtr
  • Then joined the main table with the Year and Qtr table based on the Index column
  • Remove the other columns, and expanded the table out
  • Then expanded year and qtr tables from the merge
  • Reordered the columns, set data types and renames some columsn

Final Result:

M-Code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRMjIwNENQSrE60UqRqYlFIDYEgUSAdGBIEJA0BCmFCSEpCHAHsgLc/RJzU4GM4MSc1GI4DZIH6XME6TcwAJLGQBIkagRkO4EtB4maQEWNgWxnqCodJVOQaCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Removed Top Rows" = Table.Skip(Source,4),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"PG", "PGName"}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"PG", "PGName"}, {{"Data", each _, type table [PG=text, PGName=text, Attribute=text, Value=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn(
    [Data],"Index",1,1)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Table.NestedJoin(
    [Custom], {"Index"},
    Year, {"Index"}, "Year",JoinKind.LeftOuter)),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each Table.NestedJoin(
    [Custom.1], {"Index"},
    Qtr, {"Index"}, "Qtr",JoinKind.LeftOuter)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom2",{"Custom.2"}),
    #"Expanded Custom.2" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom.2", {"PG", "PGName", "Value", "Year", "Qtr"}, {"PG", "PGName", "Value", "Year", "Qtr"}),
    #"Expanded Year" = Table.ExpandTableColumn(#"Expanded Custom.2", "Year", {"Year"}, {"Year"}),
    #"Expanded Qtr" = Table.ExpandTableColumn(#"Expanded Year", "Qtr", {"QTR"}, {"QTR"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Qtr",{"PG", "PGName", "Year", "QTR", "Value"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns",{{"PG", Int64.Type}, {"PGName", type text}, {"Year", Int64.Type}, {"QTR", Int64.Type}, {"Value", Currency.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Value", "Sales"}})
in
    #"Renamed Columns"

Pbix file:

https://1drv.ms/u/s!Amqd8ArUSwDS1SckTQPBDPrH1uZz