Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
khat
Regular Visitor

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:

khat_0-1706125474136.png

 

This is how I need it to look:

khat_1-1706125512916.png

 

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

1 ACCEPTED SOLUTION
HotChilli
Super User
Super User

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

After that, you can Merge the columns.

View solution in original post

4 REPLIES 4
dufoq3
Super User
Super User

Hi @khat,

 

dufoq3_0-1706517632882.png

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.

dufoq3_1-1706517818027.png

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

AlienSx
Super User
Super User

@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

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

HotChilli
Super User
Super User

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

After that, you can Merge the columns.

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.