Forum Discussion
How to prepare multiple rows with two different colums
Hy,
I would kindly ask your help. I have a table with many columns from SQL database. This database contain one colum which contain two ore more ID spearated with " ; " (semicolons). I can split column to separate columns. However I do not know how can I prepare multiple rows (other column data should be the same but the ID column sholud be different in Power Query?
Original table:
| ID | Data 1 | Data 2 |
| 1000; 1001 | a | b |
| 1002 | c | d |
| 1003; 1004; 1005 | e | f |
My actual stage: (with split colum)
| ID1 | ID2 | ID3 | Data 1 | Data 2 |
| 1000 | 1001 | a | b | |
| 1002 | c | d | ||
| 1003 | 1004 | 1005 | e | f |
Neaded table:
| ID | Data 1 | Data 2 |
| 1000 | a | b |
| 1001 | a | b |
| 1002 | c | d |
| 1003 | e | f |
| 1004 | e | f |
| 1005 | e | f |
Best regards,
Coby
4 Replies
=#table(Table.ColumnNames(OriginalTable),List.TransformMany(Table.ToRows(OriginalTable),each Text.Split(_{0},";"),(x,y)=>{y}&List.Skip(x)))
Thanks. It is work.
Could you please define the column where should only split the data by ";" . I have other columns which contain ";" as well, but I would not split those.
this code only split the first column
Below are the results code...
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nRR0lFySSxJVDCEMYyUYnWilQwNDAysFYAkSDwRiJNgwkZATjIQp8AEjMHqTMCkKVAiFYjTlGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", type text}, {"Data 1", type text}, {"Data 2", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type1", "ID", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"ID.1", "ID.2", "ID.3"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"ID.1", Int64.Type}, {"ID.2", Int64.Type}, {"ID.3", Int64.Type}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type2", {"Data 1", "Data 2"}, "Attribute", "ID"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"ID", "Data 1", "Data 2"})
in
#"Reordered Columns"
