Need to convert a text file to Excel - not the typical comma delineated file

Maria Baker 

That's bit another logic of this file. What to do

- since now you have commas within texts, ignore them as default delimiter. That could be done as

= Csv.Document(File.Contents(filePathName),{"Column1"},{0})

which says we split on one Column1 starting from position 0. Other words, no split.

- transforming column we select as names not only field starts by _, but also equal to name

Table.AddColumn(Source, "Names", each if Text.StartsWith([Column1.1],"_") or [Column1.1] = "name" then [Column1.2] else null)

and filter them as other fields as

= Table.SelectRows(#"Filled Down", each not ( Text.StartsWith([Column1.1], "_") or [Column1.1]="name"))

- since field role could be repeated for same person, we created in the middle intermediate table with this field only

group it by names and field (Column1.1), extract list with roles and convert it to text:

after filter field role from main table and append to it above one

- datetime text here is without separation of date and time in the text. Thus we remove comma on the end and insert T between date and time

= Table.ReplaceValue(#"Extracted Text Before Delimiter",each [last_date_accessed], each Text.Insert([last_date_accessed],10,"T"),Replacer.ReplaceText,{"last_date_accessed"})

convert now this text on actual datetime.

- if in another file there is no field last_date_accessed (as in initial one) error appears. If so correct on proper field name or remove the step. That could be handled automatically, but better to know all possible datetime field names.

Please check in attached file.