Create multiple rows from a single row

[gigotomo]

I have the below table

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

  

What can be the power query code for this

[BA_Pete · Accepted solution]

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:

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

Output:

Pete

[AlienSx · Accepted solution]

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 · Accepted solution]

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:

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

Output:

Pete