cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Post Prodigy

## Power Query - Split data onto individual rows

Hi

I have a table in power bi inported from Excel. The rable contains reference number, address, tyoe, materials and height.

Some of the rows have more than 1 refrence number in the cell. The is a ; between each of these numbers.

 Reference number Address Type Materials Height 200 Flats Dax Street Flats Brick 3 300 Flats Calculate Road Flats Wood 4 400; 500; 600 Flats Evaluate Road Flats Brick 6 100 Flats Syntax  Street Flats Brick 3 700; 800; 900 Flats Context Road Flats Brick 4 50 Flats Community Street Flats Wood 8

Is it possible for power query inside of power bi to split these combined reference numbers into now rows. I would like the supporting data in the other columns to also be added to these snew rows.

The result I would exspect is

 Reference number Address Type Materials Height 200 Flats Dax Street Flats Brick 3 300 Flats Calculate Road Flats Wood 4 400 Flats Evaluate Road Flats Brick 6 500 Flats Evaluate Road Flats Brick 6 600 Flats Evaluate Road Flats Brick 6 100 Flats Syntax  Street Flats Brick 3 700 Flats Context Road Flats Brick 4 800 Flats Context Road Flats Brick 4 900 Flats Context Road Flats Brick 4 50 Flats Community Street Flats Wood 8

thank you

Richard

1 ACCEPTED SOLUTION
Super User

Hey @cottrera ,

yes, that's possible. You can split the values of the columns also by row and not just by column.

I recorded a quick video how you can do that:

As an alternative the result:

``````let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwUNJRcstJLClWcEmsUAguKUpNLYEJAWmnoszkbCBtrBSrE61kjKTcOTEnuRTISlUIyk9MQdISnp8P4pqAdZgYGFgrmIIIMyS9rmWJOaVYtMJsMwPrNUTSEVyZVwJ0HyEHmoNssgARlshOzc8rSa0owWUbxKWmyOpzc0vzMksqMa2Des5CKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Reference number" = _t, Address = _t, Type = _t, Materials = _t, Height = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Reference number", type text}, {"Address", type text}, {"Type", type text}, {"Materials", type text}, {"Height", Int64.Type}}),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Reference number", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Reference number"),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Reference number", Int64.Type}})
in
#"Changed Type1"``````

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

2 REPLIES 2
Post Prodigy

Perfect thank you for your quick response😀

Super User

Hey @cottrera ,

yes, that's possible. You can split the values of the columns also by row and not just by column.

I recorded a quick video how you can do that:

As an alternative the result:

``````let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwUNJRcstJLClWcEmsUAguKUpNLYEJAWmnoszkbCBtrBSrE61kjKTcOTEnuRTISlUIyk9MQdISnp8P4pqAdZgYGFgrmIIIMyS9rmWJOaVYtMJsMwPrNUTSEVyZVwJ0HyEHmoNssgARlshOzc8rSa0owWUbxKWmyOpzc0vzMksqMa2Des5CKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Reference number" = _t, Address = _t, Type = _t, Materials = _t, Height = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Reference number", type text}, {"Address", type text}, {"Type", type text}, {"Materials", type text}, {"Height", Int64.Type}}),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Reference number", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Reference number"),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Reference number", Int64.Type}})
in
#"Changed Type1"``````

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards
Denis

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.