Forum Discussion
Solved
Power Query: Split single row into multiple Rows
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 replac...
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
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