Row to Column 5 rows of data separated by null row

[SOLTEC]

I have a very large spreadsheet that I have manually separated Name, address, city, state and zip code, with the phone number in Column A and I want to put name in column B, address in column C, city in column D, State and Zip in Column D, and finally the phone in column E. See attached image. Thank you in advance for any and all assistance as it is greatly appreciated.

 

[SOLTEC]

Row of all 5 columns with null between	Name	Address	City	State Zip	Telephone
Miller-Huebl Funeral Home	Miller-Huebl Funeral Home	1111 South Main Street	Aberdeen	, SD 57401	(605) 225-8223
1111 South Main Street	Schriver's Memorial Mortuary	414 5th Avenue Nw	Aberdeen	, SD 57401	(605) 225-0691
Aberdeen	Spitzer-Osthus Funeral Home	320 6th Avenue Se	Aberdeen	, SD 57401	(605) 225-7025
, SD 57401
(605) 225-8223
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

 

[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(IndexTFR, {{"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

raw		Name	Address	City	State Zip	Telephone
Miller-Huebl Funeral Home		Miller-Huebl Funeral Home	1111 South Main Street	Aberdeen	, SD 57401	(605) 225-8223
1111 South Main Street		Schriver's Memorial Mortuary	414 5th Avenue Nw	Aberdeen	, SD 57401	(605) 225-0691
Aberdeen		Spitzer-Osthus Funeral Home	320 6th Avenue Se	Aberdeen	, SD 57401	(605) 225-7025
, SD 57401
(605) 225-8223
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

 

[SOLTEC]

Expression.Error: We couldn't find an Excel table named 'Table1'.
Details:
Table1

 

[sandy666]

blue table is Table1

add header "raw" then select whole blue table and use Ctrl+T