Power query function - loop for simple PQ functuin

Hi nirrobi,

I'd like to suggest you use csv format to store the data.(I test to use position and length to split columns, but I find it only support use fixed number of characters to split column.)

Sample:

HDR,4324,645654,20161111
L01,345,456465,20151010
L01,1111,456789,20160303
L02,5555,777777,20160909

Write the function to load data and format table.

FormatList: Format table path, the column name field

let
    FormatList=(FilePath as text,Name as text) as list =>
let
    Source = Excel.Workbook(File.Contents(FilePath), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet),
    Custom= Table.ToList(Table.SelectColumns(#"Promoted Headers",Name))
in
    Custom
in
    FormatList

LoadData: Data file path, Format table path, Column name field

let
    LoadData=(DataPath as text,FormatFilePath as text,ColumnName as text) as table =>
let
    Source = Table.FromColumns({Lines.FromBinary(File.Contents(DataPath))}),
    #"Split" = Table.SplitColumn(Source,"Column1",Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv),FormatSource(FormatFilePath,ColumnName))
in
    #"Split"
in
    LoadData

Use:

"Name" means column name is store in Name field.

Notice: custom function only support at desktop side.

Regards,

Xiaoxin Sheng