cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nirrobi Member
Member

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.

 

7 REPLIES 7
Highlighted
BhaveshPatel Super Contributor
Super Contributor

Re: Power query function - loop for simple PQ functuin

Can you please post the sample data from both the tables. 

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.
nirrobi Member
Member

Re: Power query function - loop for simple PQ functuin

Hi,

 

Thanks for your reply.

I upload sample of the 2 file:

 

1-Original File

https://www.dropbox.com/s/pu5anwdbmh00pk6/Samlpe.txt?dl=0

2-Translation Table

https://www.dropbox.com/s/ixufczk4j9cc37o/Sample.xlsx?dl=0

 

 

nirrobi Member
Member

Re: Power query function - loop for simple PQ functuin

Hi,

 

Thanks for your reply.

I upload sample of the 2 file:

 

1-Original File

https://www.dropbox.com/s/pu5anwdbmh00pk6/Samlpe.txt?dl=0

2-Translation Table

https://www.dropbox.com/s/ixufczk4j9cc37o/Sample.xlsx?dl=0

 

 

Community Support Team
Community Support Team

Re: Power query function - loop for simple PQ functuin

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.

 

Capture.PNG

 

2. Load it to query editor.

Capture2.PNG

 

3. Click on "Use First Row As Headers"

Capture3.PNG

 

Capture4.PNG

 

4. Change the columns type to finish the loading.

 

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
nirrobi Member
Member

Re: Power query function - loop for simple PQ functuin

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.

Community Support Team
Community Support Team

Re: 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:

Capture.PNGCapture2.PNG

 

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

Capture3.PNG

 

Notice: custom function only support at desktop side.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
nirrobi Member
Member

Re: Power query function - loop for simple PQ functuin

thanks a lot!

 

Unfortunately it's not what I am looking for 😞

 

I want to build (simple) function that take as parameter 3 variable:

  1. name of column
  2. start position
  3. number of characters

then I insert to the function my txt file file with the paramter and I got as many columns the table had with relevant data inside.

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 158 members 1,769 guests
Please welcome our newest community members: