Power Query - create multiple rows

[jerryr125]

Hi - I would like to do the following - create a row for each value in a field.

Example:

ID	Food Type
1	Ice Cream; Cake; Cookies
2	Apples; Bananas; Pears; Peaches
3	Pizza

Output:

ID	Food Type
1	Ice Cream
1	Cake
1	Cookies
2	Apples
2	Bananas
2	Peaches
2	Pears
3	Pizza

Therefore, the ";" indicates a new record (so to speak) - any thoughts ? Jerry

[ronrsnfld · Accepted solution]

Source

You turn the string into a List, and then expand it:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfJMTlVwLkpNzLVWcE7MTgWS+fnZmanFSrE60UpGQAWOBQU5qcXWCk6JeUAIZASkJhZBqOQMqDpjoLqAzKqqRKXYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Food Type" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Food Type", type text}}),
    #"Create List" = Table.TransformColumns(#"Changed Type",{"Food Type", each Text.Split(_,";"),type {text}}),
    #"Expanded Food Type" = Table.ExpandListColumn(#"Create List", "Food Type")
in
    #"Expanded Food Type"

Result

[AntrikshSharma · Accepted solution]

@jerryr125 Many ways to solve this, here is one using List.TransformMany

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfJMTlVwLkpNzLVWcE7MTgWS+fnZmanFSrE60UpGQAWOBQU5qcXWCk6JeUAIZASkJhZBqOQMqDpjoLqAzKqqRKXYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Food Type" = _t]),
    ChangedType =
        Table.TransformColumnTypes ( Source, { { "ID", Int64.Type } } ), 
    Transform = 
        List.TransformMany (
            Table.ToRows ( ChangedType ), 
            ( z ) => Text.SplitAny ( z{1}, ";" ), 
            ( x, y ) => { x{0}, y }
        ), 
    ToTable = 
        Table.FromRows ( 
            Transform, 
            type table [ ID = Int64.Type, Food Type = text ] 
        )
in
    ToTable

[Omid_Motamedise · Accepted solution]

Hi @jerryr125 , you can solve this problem by UI, just select the column Food Type, go to the homw tab, select Split Column by delimiter and make the setting presented in the below picture (it is important to select by row)

[AntrikshSharma · Accepted solution]

@jerryr125 Many ways to solve this, here is one using List.TransformMany

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfJMTlVwLkpNzLVWcE7MTgWS+fnZmanFSrE60UpGQAWOBQU5qcXWCk6JeUAIZASkJhZBqOQMqDpjoLqAzKqqRKXYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Food Type" = _t]),
    ChangedType =
        Table.TransformColumnTypes ( Source, { { "ID", Int64.Type } } ), 
    Transform = 
        List.TransformMany (
            Table.ToRows ( ChangedType ), 
            ( z ) => Text.SplitAny ( z{1}, ";" ), 
            ( x, y ) => { x{0}, y }
        ), 
    ToTable = 
        Table.FromRows ( 
            Transform, 
            type table [ ID = Int64.Type, Food Type = text ] 
        )
in
    ToTable

[jerryr125]

Hi - I did a split then a unpivot and solved it - thanks so much - Jerry

[ronrsnfld · Accepted solution]

Source

You turn the string into a List, and then expand it:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfJMTlVwLkpNzLVWcE7MTgWS+fnZmanFSrE60UpGQAWOBQU5qcXWCk6JeUAIZASkJhZBqOQMqDpjoLqAzKqqRKXYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Food Type" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Food Type", type text}}),
    #"Create List" = Table.TransformColumns(#"Changed Type",{"Food Type", each Text.Split(_,";"),type {text}}),
    #"Expanded Food Type" = Table.ExpandListColumn(#"Create List", "Food Type")
in
    #"Expanded Food Type"

Result