Formatting part numbers

[khat]

I need to format some part numbers in Power Query but can't figure out how to do it in an automated way.

This is what the raw data looks like:

This is how I need it to look:

Someone told me this could be done with JSON but I wasn't hoping something else might work before I go to that.

Thank you

[dufoq3]

Hi @khat,

let
    SampleSource = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyNjQyNjFV0lEytDa1tlCK1YlWcvfwMje1sASKWVhbWhuaggV9/fxNjUzMQAoNrA2NrM2UYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    Split = Table.TransformColumns(SampleSource, {{"Column2", each Text.Split(_, ";")}}),
    ExpandedSplit = Table.ExpandListColumn(Split, "Column2"),
    MergedColumns = Table.CombineColumns(ExpandedSplit,{"Column1", "Column2"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Merged")
in
    MergedColumns

If you want to refer your data, delete this part and refer to your table (or previous step). Don't forget comma.

[AlienSx]

@khat 

let
    Source = your_raw_table,
    rows = Table.ToRows(Source),
    to_list = List.TransformMany(
        rows,
        (x) => Text.Split(x{1}, ";"),
        (x, y) => {x{0} & "-" & y}
    ),
    to_tbl = #table({"desired format"}, to_list)
in
    to_tbl

[khat]

Dumb question here: do I create a new column and put the suggested code in there? Unsure of the first step.

[HotChilli · Accepted solution]

use 'split column by delimiter' and (in the advanced section) choose 'To rows'.

After that, you can Merge the columns.