Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
yosemite
Helper III
Helper III

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

 

Desired Data

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

@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

 

DataInsights_0-1604182333218.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

5 REPLIES 5
v-yuaj-msft
Community Support
Community Support

Dear @yosemite ,

 

Based on your description, you can do some steps as follows.

  1. Create an index column in Power Query.
  2. Merge ‘contact’ column and ‘telephone’ column as a ‘merged’ column, then delete both columns. (‘Tab’ separator)
  3. Select the "index" column to pivot the column. Select "Do not aggregate".

v-yuaj-msft_4-1604363262039.png

 

v-yuaj-msft_5-1604363262041.png

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

v-yuaj-msft_0-1604364823747.png

 

v-yuaj-msft_1-1604364823750.png

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

v-yuaj-msft_1-1604363219943.png

 

v-yuaj-msft_2-1604363219948.png

6. Rename the newly created column.

v-yuaj-msft_3-1604363219950.png

 

Result:

v-yuaj-msft_0-1604363195458.png

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.

yosemite
Helper III
Helper III

@DataInsights , @Ashish_Mathur  Thank you!

You are welcome.  If my reply helped, please mark it as Answer.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Ashish_Mathur
Super User
Super User

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
DataInsights
Super User
Super User

@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

 

DataInsights_0-1604182333218.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.