Power Query Editor

Hi heetu24, two different approaches here.

Result

v1

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8vRzUdJRMgRiRyCOAOKUjOIMIBUZApJwTc7Py8+tBLICilJzSzNzgazg0oLUIgUQPzezFCRgZAEizEDmmCrF6kQrOXv4gUSA2AlkEhAX5xXnJQFpX8cQFLOwmW8INg7kJEMTiHGOIOOMgdgZ6sbijGIgGViaWFSSWoTNSYQFwLYYmkFcHhsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Country = _t, #"Product ID" = _t, Company = _t, #"Item name" = _t, #"Brand name" = _t, #"Time Group" = _t, #"Price Tier CY" = _t, #"Price  Tier LY" = _t, #"Price Tier 2LY" = _t, #"Sales Value CY" = _t, #"Sales Value LY" = _t, #"Sales Value L2Y" = _t]),
    // This removes extra spaces in column names (in your sample there were 2 spaces in 'Price  Tier LY' column name)
    ColnamesCleaned = Table.TransformColumnNames(Source, each Text.Combine(List.RemoveItems(Text.Split(_, " "), {""}), " ")),
    ChangedType = Table.TransformColumnTypes(ColnamesCleaned,{{"Product ID", Int64.Type}, {"Sales Value CY", type number}, {"Sales Value LY", type number}, {"Sales Value L2Y", type number}}),
    AddedIndex = Table.AddIndexColumn(ChangedType, "Index", 0, 1, Int64.Type),
    ColumnsToTransform = List.Select(Table.ColumnNames(AddedIndex), (x)=> List.Contains({"Price Tier", "Sales Value"}, x, (y,z)=> Text.Contains(z,y, Comparer.OrdinalIgnoreCase))),
    ColCount = List.Count(ColumnsToTransform),
    RemovedOtherColumns = Table.SelectColumns(AddedIndex, ColumnsToTransform),
    ToTable = Table.FromList(List.Transform(Table.ToRows(RemovedOtherColumns), each Table.FromRows(List.Zip(List.Split(_, ColCount / 2) & {List.Transform(List.Select(Table.ColumnNames(RemovedOtherColumns), each Text.StartsWith(_, "Price Tier", Comparer.OrdinalIgnoreCase)), (x)=> Text.AfterDelimiter(Text.Upper(x), "TIER "))}), type table[Price Tier=text, Values=number, Metric=text])), (x)=> {x}, type table[tbl=table]),
    AddedIndex2 = Table.AddIndexColumn(ToTable, "Index", 0, 1, Int64.Type),
    StepBackAndRemovedColumns = Table.RemoveColumns(AddedIndex, ColumnsToTransform),
    MergedQueries = Table.NestedJoin(StepBackAndRemovedColumns, {"Index"}, AddedIndex2, {"Index"}, "AddedIndex2", JoinKind.LeftOuter),
    ExpandedAddedIndex2 = Table.ExpandTableColumn(MergedQueries, "AddedIndex2", {"tbl"}, {"tbl"}),
    Expandedtbl = Table.ExpandTableColumn(ExpandedAddedIndex2, "tbl", {"Metric", "Price Tier", "Values"}, {"Metric", "Price Tier", "Values"}),
    RemovedColumns = Table.RemoveColumns(Expandedtbl,{"Index"}),
    AddedPrefix = Table.TransformColumns(RemovedColumns, {{"Metric", each "SV " & _, type text}}),
    ChangedType2 = Table.TransformColumnTypes(AddedPrefix,{{"Price Tier", type text}, {"Values", type number}, {"Metric", type text}})
in
    ChangedType2

v2

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8vRzUdJRMgRiRyCOAOKUjOIMIBUZApJwTc7Py8+tBLICilJzSzNzgazg0oLUIgUQPzezFCRgZAEizEDmmCrF6kQrOXv4gUSA2AlkEhAX5xXnJQFpX8cQFLOwmW8INg7kJEMTiHGOIOOMgdgZ6sbijGIgGViaWFSSWoTNSYQFwLYYmkFcHhsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Country = _t, #"Product ID" = _t, Company = _t, #"Item name" = _t, #"Brand name" = _t, #"Time Group" = _t, #"Price Tier CY" = _t, #"Price  Tier LY" = _t, #"Price Tier 2LY" = _t, #"Sales Value CY" = _t, #"Sales Value LY" = _t, #"Sales Value L2Y" = _t]),
    // This removes extra spaces in column names (in your sample there were 2 spaces in 'Price  Tier LY' column name)
    ColnamesCleaned = Table.TransformColumnNames(Source, each Text.Combine(List.RemoveItems(Text.Split(_, " "), {""}), " ")),
    ChangedType = Table.TransformColumnTypes(ColnamesCleaned,{{"Product ID", Int64.Type}, {"Sales Value CY", type number}, {"Sales Value LY", type number}, {"Sales Value L2Y", type number}}),
    Helper = [ ColumnsToTransform = List.Select(Table.ColumnNames(ChangedType), (x)=> List.Contains({"Price Tier", "Sales Value"}, x, (y,z)=> Text.Contains(z,y, Comparer.OrdinalIgnoreCase))),
    ColCount = List.Count(ColumnsToTransform),
    ColsZip = List.Buffer(List.Zip(List.Split(ColumnsToTransform, ColCount / 2))),
    Metric = List.Transform(List.Select(ColumnsToTransform, (x)=> Text.StartsWith(x, "Price Tier", Comparer.OrdinalIgnoreCase)), (y)=> "SV " & Text.Trim(Text.AfterDelimiter(Text.Upper(y), "TIER "))) ],
    MergedColumns = List.Accumulate(
        {0..List.Count(Helper[Metric])-1},
        ChangedType,
        (s,c)=> Table.CombineColumns(Table.TransformColumnTypes(s, {{Helper[ColsZip]{c}{1}, type text}}, "sk-SK"),{Helper[ColsZip]{c}{0}, Helper[ColsZip]{c}{1}}, Combiner.CombineTextByDelimiter("||", QuoteStyle.None), Helper[Metric]{c} ) ),
    UnpivotedColumns = Table.UnpivotOtherColumns(MergedColumns, List.RemoveItems(Table.ColumnNames(MergedColumns), Helper[Metric]) , "Metric", "Value"),
    SplitColumnByDelimiter = Table.SplitColumn(UnpivotedColumns, "Value", Splitter.SplitTextByDelimiter("||", QuoteStyle.Csv), {"Price Tier", "Values"}),
    ChangedType2 = Table.TransformColumnTypes(SplitColumnByDelimiter,{{"Values", type number}})
in
    ChangedType2