Forum Discussion
Solved
To transpose data in power query editor
Hello I have got a table which has more than one contact number for each id. This is shown in table 1 as below. Table 1 ID Telecom Number DOB 1 01522 7876543 01/01/2000 1 0789654...
Please see below. I tried to make it dynamic in case you don't have unifom Telecom Number counts per ID.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dY5LDoAgDAWvQro2oR9K4SyG+1/DUo0mqAkLOvOgb9+BYAMkZU7WrGqRmLMfRkQY2xWx1qdlensOJCzJP2ApESkZdYlYb0bu1Yf0jZdnEknHmt1Q7w8+O2v1XlZ/IuFm6XvhxLGrmt9gjAM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Telecom Number" = _t, DOB = _t]), Trim = Table.TransformColumns(Source,{{"Telecom Number", Text.Trim, type text}, {"DOB", Text.Trim, type text}}), Group = Table.Group(Trim, {"ID"}, {{"Gr", each Text.Combine(List.RemoveMatchingItems(List.Distinct(_[Telecom Number]), {""}) ,"|")}, {"DOB", each List.RemoveMatchingItems(List.Distinct([DOB]),{""}){0}}}), Count = {1..List.Max(List.Transform(Group[Gr], each List.Count(Text.Split(Text.From(_),"|"))))}, Split = Table.SplitColumn(Group, "Gr", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), List.Transform(Count, each "Telecom Number " & Text.From(_)) ) in SplitSource
Result
Please see below. I tried to make it dynamic in case you don't have unifom Telecom Number counts per ID.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dY5LDoAgDAWvQro2oR9K4SyG+1/DUo0mqAkLOvOgb9+BYAMkZU7WrGqRmLMfRkQY2xWx1qdlensOJCzJP2ApESkZdYlYb0bu1Yf0jZdnEknHmt1Q7w8+O2v1XlZ/IuFm6XvhxLGrmt9gjAM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"Telecom Number" = _t, DOB = _t]),
Trim = Table.TransformColumns(Source,{{"Telecom Number", Text.Trim, type text}, {"DOB", Text.Trim, type text}}),
Group = Table.Group(Trim, {"ID"}, {{"Gr", each Text.Combine(List.RemoveMatchingItems(List.Distinct(_[Telecom Number]), {""}) ,"|")}, {"DOB", each List.RemoveMatchingItems(List.Distinct([DOB]),{""}){0}}}),
Count = {1..List.Max(List.Transform(Group[Gr], each List.Count(Text.Split(Text.From(_),"|"))))},
Split = Table.SplitColumn(Group, "Gr", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), List.Transform(Count, each "Telecom Number " & Text.From(_)) )
in
SplitSource
Result
Thankyou for your quick response.
