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
gigotomo
Frequent Visitor

Create multiple rows from a single row

I have the below table

 

 

Screenshot 2025-07-18 071251.jpg

 

I want to create another column called "Local Market" which splits this column based on semi-column and the values come on a new row like below

Screenshot 2025-07-18 071327.jpg  

 

What can be the power query code for this

2 ACCEPTED SOLUTIONS
BA_Pete
Super User
Super User

Hi @gigotomo ,

 

Try the following example code to see how I did this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnG1DvW2dg1WitWJVnL0AXHcgpRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Local Market Combined" = _t]),

// Relevant steps=====>
    dupeColumn = Table.DuplicateColumn(Source, "Local Market Combined", "Local Market"),
    splitByDelimToRows = Table.ExpandListColumn(Table.TransformColumns(dupeColumn, {{"Local Market", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Local Market")
in
    splitByDelimToRows

 

Summary:

-1- Duplicate [Local Market Combined], changing the new column name to the desired value in the duplicate step code:

BA_Pete_1-1752823563485.png

 

-2- Select your new [Local Market] column and go to Transform tab > Split Column (dropdown) > By Delimiter. Set up this as follows:

BA_Pete_0-1752823528251.png

 

Output:

BA_Pete_0-1752823755133.png

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

AlienSx
Super User
Super User

let
    Source = #table({"Local Market Combined"}, {{"DE;UK;ES"}, {"AL;UK;FR"}}), 
    result = Table.FromList(
        List.TransformMany(
            Table.ToList(Source, (x) => x),
            (x) => Text.Split(x{0}, ";"),
            (x, y) => x & {y}
        ),
        (x) => x, 
        {"Local Market Combined", "Local Market"}
    )
in
    result

View solution in original post

2 REPLIES 2
AlienSx
Super User
Super User

let
    Source = #table({"Local Market Combined"}, {{"DE;UK;ES"}, {"AL;UK;FR"}}), 
    result = Table.FromList(
        List.TransformMany(
            Table.ToList(Source, (x) => x),
            (x) => Text.Split(x{0}, ";"),
            (x, y) => x & {y}
        ),
        (x) => x, 
        {"Local Market Combined", "Local Market"}
    )
in
    result
BA_Pete
Super User
Super User

Hi @gigotomo ,

 

Try the following example code to see how I did this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnG1DvW2dg1WitWJVnL0AXHcgpRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Local Market Combined" = _t]),

// Relevant steps=====>
    dupeColumn = Table.DuplicateColumn(Source, "Local Market Combined", "Local Market"),
    splitByDelimToRows = Table.ExpandListColumn(Table.TransformColumns(dupeColumn, {{"Local Market", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Local Market")
in
    splitByDelimToRows

 

Summary:

-1- Duplicate [Local Market Combined], changing the new column name to the desired value in the duplicate step code:

BA_Pete_1-1752823563485.png

 

-2- Select your new [Local Market] column and go to Transform tab > Split Column (dropdown) > By Delimiter. Set up this as follows:

BA_Pete_0-1752823528251.png

 

Output:

BA_Pete_0-1752823755133.png

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

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