Convert Vertical List to 3 Column table in Power Query

[mmunozjr5]

Hello, I have a dataset that came in a single column. The list is consistently structured to include a Customer's Name, Address, and Amount. as shown below.

John Doe
123 Main Street
123.45
James Smith
587 West Avenue
547.36
Robert Polk
639 Howard Blvd.
974.22

I would like to convert this vertical list into a 3-column table as shown below in Power Query.

 Name        | Address          | Amount
-------------|------------------|----------|--
 Jhon Doe    | 123 Main Street  | 123.45
 James Smith | 587 West Avenue  | 547.36
 Robert Polk | 639 Howard Blv.  | 974.22

Thank you for your assistance!

[Shravan133 · Accepted solution]

To transform your single-column vertical list into a structured three-column table in Power Query, follow these steps:

Steps to Transform Data in Power Query

1. Load Your Data into Power Query:
• Load your data into Power Query. If your data is in Excel or another source, select the data and choose "Transform Data" to open Power Query.
2. Add an Index Column:
• In Power Query, go to the "Add Column" tab and select "Index Column" -> "From 1". This will help you differentiate between rows.
3. Add Custom Column to Identify Rows:
• Add a custom column to identify the type of data in each row. Go to "Add Column" -> "Custom Column" and use the following formula:

if Number.Mod([Index], 3) = 1 then "Name"

else if Number.Mod([Index], 3) = 2 then "Address"

else "Amount"

• This formula uses the index to determine whether the row contains a Name, Address, or Amount.
1. Pivot the Data:
• Go to the "Transform" tab, and select "Pivot Column".
• In the "Pivot Column" dialog, set "Custom Column" (created in the previous step) as the pivot column.
• For the "Values Column", choose the original column that contains your data.
2. Rename Columns:
• Rename the columns to "Name", "Address", and "Amount" as appropriate.
3. Adjust Data Types:
• Make sure that the "Amount" column is formatted as a number. Select the "Amount" column, go to the "Transform" tab, and choose the appropriate number format (e.g., Decimal Number).

Example M Code

Here is an example of how the M code might look after performing these transformations:

let

    // Load the source data

    Source = Excel.CurrentWorkbook(){[Name="YourTableName"]}[Content],

    // Add an index column

    AddIndex = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),

    // Add a custom column to identify the row type

    AddCustomColumn = Table.AddColumn(AddIndex, "CustomColumn", each if Number.Mod([Index], 3) = 1 then "Name" else if Number.Mod([Index], 3) = 2 then "Address" else "Amount"),

    // Pivot the data

    PivotedTable = Table.Pivot(AddCustomColumn, List.Distinct(AddCustomColumn[CustomColumn]), "CustomColumn", "Column1"),

    // Rename columns

    RenamedColumns = Table.RenameColumns(PivotedTable, {{"Name", "Name"}, {"Address", "Address"}, {"Amount", "Amount"}}),

    // Change column types

    ChangedType = Table.TransformColumnTypes(RenamedColumns, {{"Amount", type number}})

in

    ChangedType

Tips

• Ensure the index column starts from 1 to match the modulo calculation.
• Verify that the data does not have extra empty rows or formatting issues that might affect the results.

By following these steps, you should be able to transform your vertical list into a structured three-column table in Power Query.

[Anonymous · Accepted solution]

Hi @mmunozjr5 ,
Thanks for @Shravan133 reply.
You can try this code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFNwyU9VitWJVjI0MlbwTczMUwguKUpNLYGJ6ZmYgpleibmpxQrBuZklGWC+qYW5QnhqcYmCY1lqXinECFMTcz1jMzAzKD8ptahEISA/JxvMNzO2VPDIL08sSlFwyilL0QMLWpqb6BkZKcXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column = _t]),
    AddIndexColumn = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    AddGroup = Table.AddColumn(AddIndexColumn, "Group", each Number.IntegerDivide([Index] - 1, 3) + 1),
    GroupedRows = Table.Group(AddGroup, {"Group"}, {{"AllData", each _, type table [Column=nullable text, Index=Int64.Type, Group=Int64.Type]}}),
    ExpandColumns = Table.TransformColumns(GroupedRows, {"AllData", each Table.Transpose(Table.RemoveColumns(_, {"Index", "Group"}))}),
    ExpandColumns2 = Table.ExpandTableColumn(ExpandColumns, "AllData", {"Column1", "Column2", "Column3"}, {"Name", "Address", "Amount"}),
    #"Removed Columns" = Table.RemoveColumns(ExpandColumns2,{"Group"})
in
    #"Removed Columns"

Final output

Best regards,
Albert He

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

[dufoq3 · Accepted solution]

Hi @mmunozjr5, different approach:

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFNwyU9VitWJVjI0MlbwTczMUwguKUpNLYGJ6ZmYgpleibmpxQrBuZklGWC+qYW5QnhqcYmCY1lqXinECFMTcz1jMzAzKD8ptahEISA/JxvMNzO2VPDIL08sSlFwyilL0QMLWpqb6BkZKcXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    Transformed = Table.FromRows(List.Split(Table.ToList(Source), 3), {"Name", "Address", "Amount"}),
    ChangedType = Table.TransformColumnTypes(Transformed,{{"Name", type text}, {"Address", type text}, {"Amount", type number}}, "en-US")
in
    ChangedType

[Anonymous · Accepted solution]

Hi @mmunozjr5 ,
Thanks for @Shravan133 reply.
You can try this code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srPyFNwyU9VitWJVjI0MlbwTczMUwguKUpNLYGJ6ZmYgpleibmpxQrBuZklGWC+qYW5QnhqcYmCY1lqXinECFMTcz1jMzAzKD8ptahEISA/JxvMNzO2VPDIL08sSlFwyilL0QMLWpqb6BkZKcXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column = _t]),
    AddIndexColumn = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    AddGroup = Table.AddColumn(AddIndexColumn, "Group", each Number.IntegerDivide([Index] - 1, 3) + 1),
    GroupedRows = Table.Group(AddGroup, {"Group"}, {{"AllData", each _, type table [Column=nullable text, Index=Int64.Type, Group=Int64.Type]}}),
    ExpandColumns = Table.TransformColumns(GroupedRows, {"AllData", each Table.Transpose(Table.RemoveColumns(_, {"Index", "Group"}))}),
    ExpandColumns2 = Table.ExpandTableColumn(ExpandColumns, "AllData", {"Column1", "Column2", "Column3"}, {"Name", "Address", "Amount"}),
    #"Removed Columns" = Table.RemoveColumns(ExpandColumns2,{"Group"})
in
    #"Removed Columns"

Final output

Best regards,
Albert He

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

[mmunozjr5]

Thank you so much for taking the time to solve this challenge. I greatly appreciate your time and effort. Thanks!

[Shravan133 · Accepted solution]

To transform your single-column vertical list into a structured three-column table in Power Query, follow these steps:

Steps to Transform Data in Power Query

1. Load Your Data into Power Query:
• Load your data into Power Query. If your data is in Excel or another source, select the data and choose "Transform Data" to open Power Query.
2. Add an Index Column:
• In Power Query, go to the "Add Column" tab and select "Index Column" -> "From 1". This will help you differentiate between rows.
3. Add Custom Column to Identify Rows:
• Add a custom column to identify the type of data in each row. Go to "Add Column" -> "Custom Column" and use the following formula:

if Number.Mod([Index], 3) = 1 then "Name"

else if Number.Mod([Index], 3) = 2 then "Address"

else "Amount"

• This formula uses the index to determine whether the row contains a Name, Address, or Amount.
1. Pivot the Data:
• Go to the "Transform" tab, and select "Pivot Column".
• In the "Pivot Column" dialog, set "Custom Column" (created in the previous step) as the pivot column.
• For the "Values Column", choose the original column that contains your data.
2. Rename Columns:
• Rename the columns to "Name", "Address", and "Amount" as appropriate.
3. Adjust Data Types:
• Make sure that the "Amount" column is formatted as a number. Select the "Amount" column, go to the "Transform" tab, and choose the appropriate number format (e.g., Decimal Number).

Example M Code

Here is an example of how the M code might look after performing these transformations:

let

    // Load the source data

    Source = Excel.CurrentWorkbook(){[Name="YourTableName"]}[Content],

    // Add an index column

    AddIndex = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),

    // Add a custom column to identify the row type

    AddCustomColumn = Table.AddColumn(AddIndex, "CustomColumn", each if Number.Mod([Index], 3) = 1 then "Name" else if Number.Mod([Index], 3) = 2 then "Address" else "Amount"),

    // Pivot the data

    PivotedTable = Table.Pivot(AddCustomColumn, List.Distinct(AddCustomColumn[CustomColumn]), "CustomColumn", "Column1"),

    // Rename columns

    RenamedColumns = Table.RenameColumns(PivotedTable, {{"Name", "Name"}, {"Address", "Address"}, {"Amount", "Amount"}}),

    // Change column types

    ChangedType = Table.TransformColumnTypes(RenamedColumns, {{"Amount", type number}})

in

    ChangedType

Tips

• Ensure the index column starts from 1 to match the modulo calculation.
• Verify that the data does not have extra empty rows or formatting issues that might affect the results.

By following these steps, you should be able to transform your vertical list into a structured three-column table in Power Query.

[mmunozjr5]

Thank you so much for taking the time to solve this challenge. I greatly appreciate your time and effort. Thanks!