PQ - Transform data from vertical to horizontal with Table.FromRecords

[sandy666]

let
TFR = Table.FromRecords({[Column = "Name"], [Column = "Address"], [Column = "City"], [Column = "State Zip"], [Column = "Telephone"]}),
USC = Table.Unpivot(TFR, {"Column"}, "Attribute", "Value"),
IndexTFR = Table.AddIndexColumn(USC, "Index", 1, 1),
TCC = Table.CombineColumns(Table.TransformColumnTypes(In dexTFR, {{"Index", type text}}, "en-GB"),{"Attribute", "Index"},Combiner.CombineTextByDelimiter(".", QuoteStyle.None),"Merged"),
Pivot = Table.Pivot(TCC, List.Distinct(TCC[Merged]), "Merged", "Value"),
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Filter = Table.SelectRows(Source, each ([raw] <> null)),
Index = Table.AddIndexColumn(Filter, "Index", 0, 1),
IDC = Table.TransformColumns(Index, {{"Index", each Number.IntegerDivide(_, 5), Int64.Type}}),
Group = Table.Group(IDC, {"Index"}, {{"Count", each _, type table}}),
List = Table.AddColumn(Group, "Column", each [Count][raw]),
Extract = Table.TransformColumns(List, {"Column", each Text.Combine(List.Transform(_, Text.From), "="), type text}),
Split = Table.SplitColumn(Extract, "Column", Splitter.SplitTextByDelimiter("=", QuoteStyle.Csv), {"Column.1", "Column.2", "Column.3", "Column.4", "Column.5"}),
RC = Table.RemoveColumns(Split,{"Index", "Count"}),
TC = Table.Combine({Pivot, RC}),
Promote = Table.PromoteHeaders(TC, [PromoteAllScalars=true])
in
Promote



sourceplanned column names in the result
result

rawNameAddressCityState ZipTelephone

Miller-Huebl Funeral HomeNameMiller-Huebl Funeral Home1111 South Main StreetAberdeen, SD 57401(605) 225-8223

1111 South Main StreetAddressSchriver's Memorial Mortuary414 5th Avenue NwAberdeen, SD 57401(605) 225-0691

AberdeenCitySpitzer-Osthus Funeral Home320 6th Avenue SeAberdeen, SD 57401(605) 225-7025

, SD 57401State Zip

(605) 225-8223Telephone



Schriver's Memorial Mortuary

414 5th Avenue Nw

Aberdeen

, SD 57401

(605) 225-0691



Spitzer-Osthus Funeral Home

320 6th Avenue Se

Aberdeen

, SD 57401

(605) 225-7025