This can be done in Power Query with the following code.
Here you go...
let
Source =
Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fY9BDsIwDAT/0nMjre3gOkcgQpw5AVX//w3ikFIqUA6TrBSN15nn4XgK5xxyDsQSh3HgQmxYIRUAIyAlMeY01cdl/FWpIOpBPPkk5r1ORl3dQWvVvarAR33eH+FyvQWNvO5cy/1GC5tKBP/J4Z/aLwVYu2ZqmrjPW22cTBOb9GStE3wzofX4soXxtpcX", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Website Sku" = _t, #"Component Qty" = _t, #"Component Weight (Lbs)" = _t, #"Component Height (in)" = _t, #"Component Length (in)" = _t, #"Component Width (in)" = _t, UPC = _t, #"Box Count " = _t]),
#"Changed Type" =
Table.TransformColumnTypes(
Source,
{{"Website Sku", type text}, {"Component Qty", Int64.Type}, {"Component Weight (Lbs)", Int64.Type}, {"Component Height (in)", Int64.Type}, {"Component Length (in)", Int64.Type}, {"Component Width (in)", Int64.Type}, {"UPC", type text}, {"Box Count ", Int64.Type}}),
addAdditionalRowColumn =
Table.AddColumn(
#"Changed Type",
"addRowColumn",
each if [Component Qty] > 1
then "1" & Text.Repeat("-1", [Component Qty] - 1)
else [Component Qty]
),
addRows =
Table.ExpandListColumn(
Table.TransformColumns(
Table.TransformColumnTypes(
addAdditionalRowColumn,
{{"addRowColumn", type text}}, "en-US"
),
{{"addRowColumn", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}
),
"addRowColumn"
),
#"Changed Type1" =
Table.TransformColumnTypes(
addRows,
{{"addRowColumn", Int64.Type}}
),
#"Removed Columns" =
Table.RemoveColumns(
#"Changed Type1",
{"Component Qty"}
),
#"Reordered Columns" =
Table.ReorderColumns(
#"Removed Columns",
{"Website Sku", "Box Count ", "UPC", "Component Weight (Lbs)", "Component Height (in)", "Component Length (in)", "Component Width (in)", "addRowColumn"}
),
#"Unpivoted Other Columns" =
Table.UnpivotOtherColumns(
#"Reordered Columns",
{"Website Sku", "Box Count "},
"Attribute",
"Value"
),
#"Grouped Rows" =
Table.Group(
#"Unpivoted Other Columns",
{"Website Sku", "Box Count "},
{{"groupedSKUs", each _, type table [Website Sku=nullable text, #"Box Count "=nullable number, Attribute=text, Value=number]}}
),
transformTable =
Table.AddColumn(
#"Grouped Rows",
"selectedColumns",
each Table.PromoteHeaders(
Table.Transpose(
Table.SelectRows(
Table.SelectColumns(
[groupedSKUs],
{"Attribute", "Value"}
),
each [Attribute] <> "addRowColumn"
)
)
)
),
#"Removed Columns1" =
Table.RemoveColumns(
transformTable,
{"groupedSKUs"}
),
getListOfColumnNames =
Table.ColumnNames(
Table.Combine(
#"Removed Columns1"[selectedColumns]
)
),
#"Converted to Table" =
Table.FromList(
getListOfColumnNames,
Splitter.SplitByNothing(),
null,
null,
ExtraValues.Error
),
extractTextAfterDelimiter =
Table.AddColumn(
#"Converted to Table",
"Text After Delimiter",
each Text.AfterDelimiter([Column1], "_"),
type text
),
determineUPCNumber =
Table.AddColumn(
extractTextAfterDelimiter,
"Custom",
each if Number.Mod(Number.From([Text After Delimiter]),5) = 0
then Number.IntegerDivide(Number.From([Text After Delimiter]),5) - 1
else Number.IntegerDivide(Number.From([Text After Delimiter]),5)
),
constructNewName =
Table.AddColumn(
determineUPCNumber,
"newName",
each if [Custom] = null
then Text.BeforeDelimiter([Column1], "_") & "_1"
else Text.BeforeDelimiter([Column1], "_") & "_" & Number.ToText([Custom] + 2)
),
#"Removed Columns2" =
Table.RemoveColumns(
constructNewName,
{"Text After Delimiter", "Custom"}
),
createRenamingList =
List.Zip(
{#"Removed Columns2"[Column1], #"Removed Columns2"[newName]}
),
expandTransformedTable =
Table.ExpandTableColumn(
#"Removed Columns1",
"selectedColumns",
getListOfColumnNames
),
#"Renamed Columns" =
Table.RenameColumns(expandTransformedTable, createRenamingList)
in
#"Renamed Columns"
