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...

Need Help

DataInsights

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

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".