Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
and then place it in new column
Thanks in advanced,
Nir.
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
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.
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
thanks a lot!
Unfortunately it's not what I am looking for 😞
I want to build (simple) function that take as parameter 3 variable:
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.
Can you please post the sample data from both the tables.
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |