Convert Vertical List to 3 Column table in Power Query

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.

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

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