Split by Positions when positions are in 3 separate columns

[memote1]

Hello!

I have a column I am trying to split into 6 columns based on position, since there are repeating instances of some delimiters, the instance number isn't always the same, and some are missing. I was able to determine the positions for splitting, now in 4 columns, but can't get them into a list to use Splitter.SplitTextByPosition.

The source data is publicy posted. I attempted to split by line break, carriage return and tab as in this article, with no luck. 

Source Data Example

Provider Name, City, State, Zip	Speciality	Credential		License	NPI	Accred Level	F=Full L=Limited	Eff. Date	Exp. Date
Abbe, Hannah   Mountain View Pain Center 63 N Quebec St Ste 300 Denver,CO 80230 (720) 749-5599	Pain Management	PA		PA7909	1083360184	1		4/27/2024	7/31/2027
Abbott, Timothy W Mountain View Pain Center 15901 E Briarwood Cir, #150 Aurora,CO 80916 (720) 749-5599	Pain Management	PA		PA2923	1023152212	1		12/19/2024	7/31/2027
Abercrombie, Chad E Alliance Health Partners 3920 N. Union Blvd, Suite 160 Colorado Springs,CO 80907 (719) 632-4754	Chiropractics	DC		CHR3773	1093917973	1		11/17/2000	7/31/2025
Abernethy, Kimberly A N/A 10093 W 100Th Pl Westminster,CO 80021	Family Medicine	DO		DR43875*	1235210048	2		6/12/2024	1/31/2027
Abernethy, Kimberly A Concentra Broomfield 290 Nickel Street Broomfield,CO 80020	Family Medicine	MD		DR43875	1235210048	1		12/10/2023	7/31/2026

Desired Result

Provider Name

Company

Address

State

Zip

Phone

Speciality

Credential

License

NPI

Accred Level

F=Full L=Limited

Eff. Date

Exp. Date

Abbe, Hannah

Mountain View Pain Center

63 N Quebec St Ste 300 Denver,CO 80230

CO

80230

(720) 749-5599

Pain Management

PA

PA7909

1.08E+09

1

4/27/2024

7/31/2027

Abbott, Timothy

W Mountain View Pain Center

15901 E Briarwood Cir, #150 Aurora,CO 80916

CO

80916

(720) 749-5599

720) 749-5599

PA

PA2923

1.02E+09

1

########

7/31/2027

Abercrombie, Chad

E  Alliance Health Partners

3920 N. Union Blvd, Suite 160 Colorado Springs,CO 80907

CO

80907

(719) 632-4754

Chiropractics

DC

CHR3773

1.09E+09

1

########

7/31/2025

Abernethy, Kimberly

A N/A

10093 W 100Th Pl Westminster,CO 80021

CO

80021

(970) 309-8649

Family Medicine

DO

DR43875*

1.24E+09

2

6/12/2024

1/31/2027

Abernethy, Kimberly

A Concentra

290 Nickel Street Broomfield,CO 80020

CO

80020

(303) 460-9339

Family Medicine

MD

DR43875

1.24E+09

1

########

7/31/2026

Current M-Code

let
Source = Excel.CurrentWorkbook(){[Name="dnld"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Provider Name, City, State, Zip", type text}, {"Speciality", type text}, {"Credential", type text}, {"License", type text}, {"NPI", Int64.Type}, {"Accred Level", Int64.Type}, {"F=Full L=Limited", type text}, {"Eff. Date", type date}, {"Exp. Date", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Name Position", each Text.Length(Text.BeforeDelimiter([#"Provider Name, City, State, Zip"]," ",2))),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Address Position", each Text.PositionOfAny([#"Provider Name, City, State, Zip"],{"0","1","2","3","4","5","6","7","8","9"},0)),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "State Zip", each Text.PositionOf([#"Provider Name, City, State, Zip"],",",Occurrence.Last)),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "Phone Position", each Text.PositionOf([#"Provider Name, City, State, Zip"],"(",1))
in
#"Added Custom3"

Thank you!

[ronrsnfld · Accepted solution]

Add this line to your code to create the list:

#"Added Custom4" = Table.AddColumn(#"Added Custom3", "Splitter List", each Splitter.SplitTextByPositions(
            {0} & List.LastN(Record.FieldValues(_),4))([#"Provider Name, City, State, Zip"]))

And, as a suggestion, instead of reading it in as an Excel table, and taking a chance on unwanted transformations caused by Excel, I would read the `csv` file directly:

Source = Csv.Document(File.Contents("full_path_to_file\export.csv"),[Delimiter=",", Columns=9, Encoding=65001, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Provider Name, City, State, Zip", type text}, {"Speciality", type text}, {"Credential", type text}, {"License", type text}, {"NPI", Int64.Type}, {"Accred Level", Int64.Type}, {"F=Full L=Limited", type text}, {"Eff. Date", type date}, {"Exp. Date", type date}}),
...

Also, you will need to build in some error checking.

If you are missing a component (eg: phone number; street address, etc) your Position finder will return incorrect results.

[memote1]

Thank you so much!