Power Query: Split single row into multiple Rows

[Swapnil_]

I have one row Suppose Row number 21 for which two values are present in column A , Two values in column B ,  Values are seperated by space in both columns and one value in Column C. I want to replace the single row by two rows.. how to do that in power query?

[Swapnil_]

@Anonymous  @AlienSx @dufoq3 

Please find the below screenshot, In first column I have employee Name, but for few records I am getting two values, the values may or may not be duplicate. so I want to create seperate record for second occurance..In this case 2 employee name but they will have same employee Id( As there is financial figure associated with it)...

[dufoq3 · Accepted solution]

Hi, I've asked for sample data in usable format and expected result based on sample data. In future - if you'll ask for help - be so kind and read other users comments please.

Before

After

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxUSAQCJR2lxKTkFCiVqhSrE62UlKSQBAQxeVAGULKisiomD0hAmIVgdqFSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    Ad_Helper = Table.AddColumn(Source, "Helper", each 
        [ a = Record.ToList(_),
          b = if List.Contains(a, "#(lf)", (x,y)=> Text.Contains(x, y)) then List.Transform(List.Zip(List.Transform(a, (x)=> Text.Split(x, "#(lf)"))), (y)=> Text.Combine(y, "||"))  else {Text.Combine(a, "||")}
        ][b], type list ),
    RemovedOtherColumns = Table.SelectColumns(Ad_Helper,{"Helper"}),
    ExpandedHelper = Table.ExpandListColumn(RemovedOtherColumns, "Helper"),
    SplitColumnByDelimiter = Table.SplitColumn(ExpandedHelper, "Helper", Splitter.SplitTextByDelimiter("||", QuoteStyle.Csv)),
    RestoreNamesAndTypes = Value.ReplaceType(SplitColumnByDelimiter, Value.Type(Source))
in
    RestoreNamesAndTypes

[Swapnil_]

hi @dufoq3, thank you for the solution.. It was first time I was looking for help in community..for sure I provide data in case I need any help

[dufoq3]

You're welcome.

[AlienSx]

grabbed data from @Anonymous 's post

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKkvMKU01VABTRko6EL4xhG8C45sqxepAlZpBpMxhUhYQviWMb2igFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ColumnA = _t, ColumnB = _t, ColumnC = _t]),
    to_list = Table.ToList(
        Source, 
        (w) => List.TransformMany(
            {w}, 
            (x) => List.Zip({Text.Split(x{0}, " "), Text.Split(x{1}, " ")}),
            (x, y) => y & {x{2}}
        )
    ),
    to_tbl = Table.FromList(List.Combine(to_list), (x) => x, Table.ColumnNames(Source))
in
    to_tbl

[Anonymous]

Hi @Swapnil_ ,

Like this?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKkvMKU01VABTRko6EL4xhG8C45sqxepAlZpBpMxhUhYQviWMb2igFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ColumnA = _t, ColumnB = _t, ColumnC = _t]),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"),
    Custom1 = Table.TransformColumns(#"Unpivoted Columns",{"Value", each Text.Split(_, " ")} ),
    #"Expanded Value" = Table.ExpandListColumn(Custom1, "Value"),
    #"Grouped Rows" = Table.Group(#"Expanded Value", {"Attribute"}, {{"Data", each _[Value]}}),
    Custom2 = Table.FromColumns(#"Grouped Rows"[Data],#"Grouped Rows"[Attribute])
in
    Custom2

 If I've misunderstood your needs, could you tell me what your expected output is?

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[dufoq3]

Provide sample data in usable format and expected result based on that. Thanks.