Forum Discussion

khat's avatar
khat
Regular Visitor
2 years ago
Solved

Formatting part numbers

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

Need Help
  • HotChilli's avatar
    HotChilli
    2 years ago

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

    After that, you can Merge the columns.

4 Replies

  • HotChilli's avatar
    HotChilli
    Community Champion
    2 years ago

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

    After that, you can Merge the columns.

  • AlienSx's avatar
    AlienSx
    Super User
    2 years ago

    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's avatar
      khat
      Regular Visitor
      2 years ago

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

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    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.