Forum Discussion

nirrobi's avatar
nirrobi
Helper V
9 years ago

Power query function - loop for simple PQ functuin

Hi,

 

I have flat file (txt file) with  a lot of data.

I also have file with translation table of this txt file (e.g.: col 1-10-->XX, col 432-439-->YYY etc.)

I start with PQ to translate the txt file to normal table (with Text.Range duction)  soI can work with.

Then realized that there must be way to do it with "simple" function.

 

Can anyone please help with this kind of function?

I want to insert to the function:

  • name of column
  • start position
  • lengnth of the string

and then place it in new column

Thanks in advanced,

Nir.

 

Need Help

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi nirrobi,

     

    In my opinion, you can directly load the text file to create the table.

     

    Sample:

    1. add the column name to txt file with the same format.

     

     

    2. Load it to query editor.

     

    3. Click on "Use First Row As Headers"

     

     

    4. Change the columns type to finish the loading.

     

    Regards,
    Xiaoxin Sheng

    • nirrobi's avatar
      nirrobi
      Helper V
      9 years ago

      Thanks a lot.

       

      My case is more complicated and need further edit.

       

      Can You please help me to create function that take 3 parameter and make the adjustment accordingly.

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        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