Hello - I'm trying to transform data with same contractor_value to separate columns; sample below. Any help is appreciated. Sample Datacontractor_valuecontacttelephone001John(123) 456-7890001Larry(456) 487-9872002Jessica(567) 821-3560002Taylor(348) 971-6150002Sinclair(879) 853-1040003Noel(465) 450-1560003Theresa(789) 453-1324004Joan(465) 798-7848005Elizabeth(798) 755-4657005Daniel(579) 879-8451 Desired Datacontractor_valuecontact1telephone1contact2telephone2contact3telephone3001John(123) 456-7890Larry(456) 487-9872 002Jessica(567) 821-3560Taylor(348) 971-6150Sinclair(879) 853-1040003Noel(465) 450-1560Theresa(789) 453-1324 004Joan(465) 798-7848 005Elizabeth(798) 755-4657Daniel(579) 879-8451

Helper III

5 years ago

Solved

How to transform data with the same value to multiple columns

Hello - I'm trying to transform data with same contractor_value to separate columns; sample below. Any help is appreciated.

Sample Data

contractor_value	contact	telephone
001	John	(123) 456-7890
001	Larry	(456) 487-9872
002	Jessica	(567) 821-3560
002	Taylor	(348) 971-6150
002	Sinclair	(879) 853-1040
003	Noel	(465) 450-1560
003	Theresa	(789) 453-1324
004	Joan	(465) 798-7848
005	Elizabeth	(798) 755-4657
005	Daniel	(579) 879-8451

Desired Data

contractor_value	contact1	telephone1	contact2	telephone2	contact3	telephone3
001	John	(123) 456-7890	Larry	(456) 487-9872
002	Jessica	(567) 821-3560	Taylor	(348) 971-6150	Sinclair	(879) 853-1040
003	Noel	(465) 450-1560	Theresa	(789) 453-1324
004	Joan	(465) 798-7848
005	Elizabeth	(798) 755-4657	Daniel	(579) 879-8451

Try this in Power Query. Copy the code starting with GroupRows and paste into your query editor.

Thanks to edhans for this technique.

let
  Source = Table.FromRows(
      Json.Document(
          Binary.Decompress(
              Binary.FromText(
                  "Tc8/a8MwEAXwr2I01RCB/p3utDdLCFnqLXhQg8ACY4PdJfn0Oal260nD+/H07n4XSmlxEpd5mPj50Ma2jQMvkYIS/WnPr3FZngVwxoBQBkKzAVMK0rrmRywEPLYNGS0teHUgXXyO81KEddQ2AbX0Go7iK0+PMeZqCAO3gJVaud1YDm5zGusQD2Wpkvr/l5J3Q1rSWofwDYVwhTVuI64eG6e/CgzExzracuDgPOZX/E4/Qy0JvBUBJGs8oM845d8lUJdikORAi75/Aw==", 
                  BinaryEncoding.Base64
                ), 
              Compression.Deflate
            )
        ), 
      let
        _t = ((type nullable text) meta [Serialized.Text = true])
      in
        type table [contractor_value = _t, contact = _t, telephone = _t]
    ),
  GroupRows = Table.Group(
      Source, 
      {"contractor_value"}, 
      {
        {"contact group", each Table.SelectColumns(_, "contact")[contact]}, 
        {"telephone group", each Table.SelectColumns(_, "telephone")[telephone]}
      }
    ),
  ExtractValuesContact = Table.TransformColumns(
      GroupRows, 
      {"contact group", each Text.Combine(List.Transform(_, Text.From), "|"), type text}
    ),
  ExtractValuesTelephone = Table.TransformColumns(
      ExtractValuesContact, 
      {"telephone group", each Text.Combine(List.Transform(_, Text.From), "|"), type text}
    ),
  SplitContact = Table.SplitColumn(
      ExtractValuesTelephone, 
      "contact group", 
      Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), 
      {"contact group.1", "contact group.2", "contact group.3"}
    ),
  SplitTelephone = Table.SplitColumn(
      SplitContact, 
      "telephone group", 
      Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), 
      {"telephone group.1", "telephone group.2", "telephone group.3"}
    ),
  RenameColumns = Table.RenameColumns(
      SplitTelephone, 
      {
        {"contact group.1", "contact1"}, 
        {"contact group.2", "contact2"}, 
        {"contact group.3", "contact3"}, 
        {"telephone group.1", "telephone1"}, 
        {"telephone group.2", "telephone2"}, 
        {"telephone group.3", "telephone3"}
      }
    )
in
  RenameColumns

5 Replies

DataInsights

Super User

5 years ago

yosemite,

Try this in Power Query. Copy the code starting with GroupRows and paste into your query editor.

Thanks to edhans for this technique.

let
  Source = Table.FromRows(
      Json.Document(
          Binary.Decompress(
              Binary.FromText(
                  "Tc8/a8MwEAXwr2I01RCB/p3utDdLCFnqLXhQg8ACY4PdJfn0Oal260nD+/H07n4XSmlxEpd5mPj50Ma2jQMvkYIS/WnPr3FZngVwxoBQBkKzAVMK0rrmRywEPLYNGS0teHUgXXyO81KEddQ2AbX0Go7iK0+PMeZqCAO3gJVaud1YDm5zGusQD2Wpkvr/l5J3Q1rSWofwDYVwhTVuI64eG6e/CgzExzracuDgPOZX/E4/Qy0JvBUBJGs8oM845d8lUJdikORAi75/Aw==", 
                  BinaryEncoding.Base64
                ), 
              Compression.Deflate
            )
        ), 
      let
        _t = ((type nullable text) meta [Serialized.Text = true])
      in
        type table [contractor_value = _t, contact = _t, telephone = _t]
    ),
  GroupRows = Table.Group(
      Source, 
      {"contractor_value"}, 
      {
        {"contact group", each Table.SelectColumns(_, "contact")[contact]}, 
        {"telephone group", each Table.SelectColumns(_, "telephone")[telephone]}
      }
    ),
  ExtractValuesContact = Table.TransformColumns(
      GroupRows, 
      {"contact group", each Text.Combine(List.Transform(_, Text.From), "|"), type text}
    ),
  ExtractValuesTelephone = Table.TransformColumns(
      ExtractValuesContact, 
      {"telephone group", each Text.Combine(List.Transform(_, Text.From), "|"), type text}
    ),
  SplitContact = Table.SplitColumn(
      ExtractValuesTelephone, 
      "contact group", 
      Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), 
      {"contact group.1", "contact group.2", "contact group.3"}
    ),
  SplitTelephone = Table.SplitColumn(
      SplitContact, 
      "telephone group", 
      Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), 
      {"telephone group.1", "telephone group.2", "telephone group.3"}
    ),
  RenameColumns = Table.RenameColumns(
      SplitTelephone, 
      {
        {"contact group.1", "contact1"}, 
        {"contact group.2", "contact2"}, 
        {"contact group.3", "contact3"}, 
        {"telephone group.1", "telephone1"}, 
        {"telephone group.2", "telephone2"}, 
        {"telephone group.3", "telephone3"}
      }
    )
in
  RenameColumns

Ashish_Mathur

Super User

5 years ago

Hi,

This M code works

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc8/a8MwEAXwr2I01RCB/p3utDdLCFnqLXhQg8ACY4PdJfn0Oal260nD+/H07n4XSmlxEpd5mPj50Ma2jQMvkYIS/WnPr3FZngVwxoBQBkKzAVMK0rrmRywEPLYNGS0teHUgXXyO81KEddQ2AbX0Go7iK0+PMeZqCAO3gJVaud1YDm5zGusQD2Wpkvr/l5J3Q1rSWofwDYVwhTVuI64eG6e/CgzExzracuDgPOZX/E4/Qy0JvBUBJGs8oM845d8lUJdikORAi75/Aw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [contractor_value = _t, contact = _t, telephone = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"contractor_value"}, "Attribute", "Value"),
    #"Merged Columns" = Table.CombineColumns(#"Unpivoted Other Columns",{"contractor_value", "Attribute"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"),
    #"Added Index" = Table.AddIndexColumn(#"Merged Columns", "Order", 1, 1, Int64.Type),
    Partition = Table.Group(#"Added Index", {"Merged"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Value", "Order", "Index"}, {"Value", "Order", "Index"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Expanded Partition", "Merged", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Merged.1", "Merged.2"}),
    #"Sorted Rows" = Table.Sort(#"Split Column by Delimiter",{{"Order", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Order"}),
    #"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Removed Columns", {{"Index", type text}}, "en-IN"),{"Merged.2", "Index"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
    #"Pivoted Column" = Table.Pivot(#"Merged Columns1", List.Distinct(#"Merged Columns1"[Merged]), "Merged", "Value")
in
    #"Pivoted Column"

Hope this helps.

yosemite
Helper III
5 years ago
DataInsights , Ashish_Mathur Thank you!
- Ashish_Mathur
  Super User
  5 years ago
  You are welcome. If my reply helped, please mark it as Answer.
Anonymous
Not applicable
5 years ago
Dear yosemite ,

Based on your description, you can do some steps as follows.
Create an index column in Power Query.
Merge ‘contact’ column and ‘telephone’ column as a ‘merged’ column, then delete both columns. (‘Tab’ separator)
Select the "index" column to pivot the column. Select "Do not aggregate".

4. Merge all columns except the first column and leave only the first column and the merged column.

5. Use ‘Tab’ delimiter to split the ‘M’ column.

6. Rename the newly created column.

Result:
I hope my suggestion can give you some help.

Best Regards,
Yuna

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.