Transpose the row data to column

Question

I have below a list of data(Sample data) in a single column in multiple rows in excel. I would like to categorize data into multiple columns like Type, Amount, Address, Beds, Bath, Parking, Area, and dwelling type.

Example please review below image.

Data is like below image

Would like to format

Sample data:

Sold by private treaty 04 Apr 2022
$755,000
2/13 Bank Street , WOLLONGONG NSW 2500
2 Beds 1 Bath 1 Parking 679m²
Townhouse
Sold by private treaty 04 Apr 2022
Benjamin Sayers
$585,000
15/50-52 Keira Street , WOLLONGONG NSW 2500
2 Beds 1 Bath 1 Parking
Apartment / Unit / Flat
Sold by private treaty 01 Apr 2022
$975,000
201/38 Atchison Street , WOLLONGONG NSW 2500
3 Beds 2 Baths 1 Parking
Apartment / Unit / Flat

Help Much appreciated. Excel or power Query.

Thanks and be safe.

Answer 1

I started with your data in Excel as Table1. I assumed that Benjamin Sayers is the buyer of the second property, which indicates that some properties would have listed buyers and some would not. Because I assumed some would have buyers, that meant I had to account for the missing fields (lines from your data set) in the records without listed buyers.

The code below turns this:

into this:

Here's the code.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type any}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if Text.StartsWith(Text.From([Column1]),"Sold") then [Index] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"Custom"}, {{"AllData", each _, type table [Column1=any, Index=number, Custom=number]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom.1", each if Table.RowCount([AllData]) < 6 then Table.InsertRows([AllData], 1,{Record.FromList(List.Repeat({""},Table.ColumnCount([AllData])),Table.ColumnNames([AllData]))}) else [AllData]),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each Table.AddIndexColumn([Custom.1], "ColIndex", 0, 1, Int64.Type)),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom.3", each Table.Transpose(Table.SelectColumns([Custom.2],"Column1"))),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom3",{"Custom.3"}),
    #"Expanded Custom.3" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom.3", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Custom.3",{{"Column1", "Type"}, {"Column2", "Seller?"}, {"Column3", "Amount"}, {"Column4", "Address"}, {"Column5", "BBP"}, {"Column6", "Dwelling Type"}}),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Renamed Columns", "BBP", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"BBP.1", "BBP.2", "BBP.3", "BBP.4"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Split Column by Character Transition",{{"BBP.1", "Beds"}, {"BBP.2", "Baths"}, {"BBP.3", "Parking"}, {"BBP.4", "Area"}})
in
    #"Renamed Columns1"

I didn't spend much time polishing it. I figured I could at least get you started.