Forum Discussion

uc's avatar
uc
Helper II
5 years ago
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

IDTelecom NumberDOB
101522 787654301/01/2000
1078965432101/01/2000
201323 765234104/05/2000
20798712345 
2079871234504/05/2000
3 05/04/1990
301522 567897605/04/1990
30789765432 
   

 

I want to re order the table 1 as  below as in table 2

Table 2

IDTelcom number 1Telcom Number 2DOB
101522 7876543078965432101/01/2000
201323 765234107987123454/05/2000
301522 578976078976543205/04/1990
    

 

Is this possible in power query? Please advise. Thanks in advance 

Need Help
  • Jakinta's avatar
    Jakinta
    5 years ago

    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
    Split

    Source

    Result

     

     

2 Replies

  • Jakinta's avatar
    Jakinta
    Solution Sage
    5 years ago

    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
    Split

    Source

    Result

     

     

    • uc's avatar
      uc
      Helper II
      5 years ago

      Thankyou for your quick response.