PQ - convert edi (txt) file to column by translation table

[Nirrobi]

[FONT=.SF UI Text][FONT=.SFUIText]Dear PQ specialists. [/FONT][/FONT]
[FONT=.SF UI Text]
[/FONT]
[FONT=.SF UI Text][FONT=.SFUIText]I face situation that I am sure that it's pretty generic but I will try to describe it as I understand it.[/FONT][/FONT]
[FONT=.SF UI Text]
[/FONT]
[FONT=.SF UI Text][FONT=.SFUIText]I have EDI file (txt format) that contains many lines with the same pattern, e.g.[/FONT][/FONT]
[FONT=.SF UI Text][FONT=.SFUIText]01xxxx1234567y123456789[/FONT][/FONT]
[FONT=.SF UI Text]
[/FONT]
[FONT=.SF UI Text][FONT=.SFUIText]And I need to break it to 3 columns according to dictionary/translation table :[/FONT][/FONT]
[FONT=.SF UI Text][FONT=.SFUIText]From column - column size - column name[/FONT][/FONT]
[FONT=.SF UI Text][FONT=.SFUIText]1 - 2 - prefix[/FONT][/FONT]
[FONT=.SF UI Text][FONT=.SFUIText]3 - 11 - container[/FONT][/FONT]
[FONT=.SF UI Text][FONT=.SFUIText]14 - 10 - BL[/FONT][/FONT]
[FONT=.SF UI Text]
The result should be as follow:
Prefix - container - BL
01 - xxxx1234 - y123456789

[/FONT]
[FONT=.SF UI Text]
[/FONT]
[FONT=.SF UI Text][FONT=.SFUIText]Just to make clear , every line in the EDI file is very long and I have many EDI files I need to translate. [/FONT][/FONT]
[FONT=.SF UI Text][FONT=.SFUIText]Every EDI file has different dictionary table. [/FONT][/FONT]
[FONT=.SF UI Text]
[/FONT]
[FONT=.SF UI Text][FONT=.SFUIText]Any suggestions to automate the process will be much appreciated. [/FONT][/FONT]
[FONT=.SF UI Text]
[/FONT]
[FONT=.SF UI Text][FONT=.SFUIText]Regards. [/FONT][/FONT]
[FONT=.SF UI Text][FONT=.SFUIText]Nir. [/FONT][/FONT]
[FONT=.SF UI Text]
[/FONT]

 

[Ken Puls]

Let's assume that the column is called "data"

Select the [data] column --> Add Column --> Extract --> First Characters --> 2
Select the [data] column --> Add Column --> Extract --> Range --> Starting Index = 2, Number of Characters = 8
Select the [date] column --> Add Column --> Extract --> Last Characters --> 10
And then rename the columns as desired.

Hope this helps,

 

[Nirrobi]

Dear Ken,
Thanks for the prompt reply.
Indeed what you wrote answer the question I asked but I prefer to do it via some function or more sophisticated way.
I have many EDI files with translation table/file so I want to have one solution to all of them.

The expected process should be as follow:

• Prepare "the brain" of the EDI to table (function or something else)
• Load EDI file to PQ
• Load translation table to PQ
• received EDIfile prepare for analysis

According to the solution you suggest, every time I need to change a lot of data I need to have this process work automatic (semi automatic also good enough )

Many thanks.

 

[Bill Szysz]

You need to create translation table (something like this above) and load it to PQ.
This is code for translation table (Name of this query is TranslationTable

let    Source = Excel.CurrentWorkbook(){[Name="TranslationTable"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"From Column", Int64.Type}, {"Column Size", Int64.Type}, {"Column Name", type text}}),
    #"Subtracted from Column" = Table.TransformColumns(#"Changed Type", {{"From Column", each _ - 1, type number}})
in
    #"Subtracted from Column"

Then you have to load your txt file to PQ (code below)

let    Source = Table.FromColumns({Lines.FromBinary(File.Contents("pathToYourTXTFile"), null, null, 1250)}),
    Splited = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByPositions(TranslationTable[From Column], false), TranslationTable[Column Name])
in
    Splited

As you can see, we need only From Column and Column Name columns (Column Size is not necessary)

And voila )

 

[Nirrobi]

One word
WOW !!

Thanks a lot, exactly what I was looking for.
Regards,
Nir.