cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

Convert Vertical List to 3 Column table in Power Query

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!

3 ACCEPTED SOLUTIONS
Solution Sage

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

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

Community Support

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

Super User

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

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

5 REPLIES 5
Super User

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

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Community Support

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

Regular Visitor

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

Solution Sage

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

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

Regular Visitor

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

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Fabric Community Update - September 2024

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

Top Solution Authors
Top Kudoed Authors