Nov 19 2021 10:19 AM
Would like it to read like this
The Livingston
6301 windhaven pkwy
Plano Tx 75093
972-801-9595
Built 1998
Website: https://grandpeaks.com/texas/the-livingston/
Ttl Units: 180
Ttl toilets: 266
Unit mix: 1 + 1 = 6, 1 + 1 = 6, 1 + 1 = 2, 1 + 1 = 16, 1 + 1 = 18, 1 + 1 = 18, 2 + 2 = 6, 2 + 2 = 16,
2 + 2 = 18, 2 + 2 = 2, 2 + 2.5 = N/A, 2 + 2.5 = 8, 3 + 2 = 28, 3 + 2 = 18
Nov 19 2021 11:40 AM
If something like this
it could be done by Power Query
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Removed Duplicates" = Table.Distinct(Source, {"Property Street"}),
Location = Table.CombineColumns(
Table.TransformColumnTypes(
#"Removed Duplicates",
{
{"Property Zip", type text}}, "en-GB"),
{"Property City", "Property State", "Property Zip"
},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Location"),
#"Keep Columns to Transform" = Table.SelectColumns(
Location,
{"Property Name", "Property Street", "Location", "Property Phone", "Year Built",
"Property Website", "Units Total", "Toilets Total", "Unit Mix"}),
#"Values as List" = Table.AddColumn(
#"Keep Columns to Transform",
"Values", each Record.FieldValues(_)),
#"Keep Lists only" = Table.SelectColumns(
#"Values as List",{"Values"}),
#"Table from lists" = Table.FromColumns( #"Keep Lists only"[Values] ),
#"Promoted Headers" = Table.PromoteHeaders(
#"Table from lists", [PromoteAllScalars=true])
in
#"Promoted Headers"
Nov 19 2021 11:58 AM
Nov 19 2021 12:24 PM
Yes, Power Query requires to invest some time to start working with it, that's not copy/paste the pattern approach.