Convert txt file according to translation table

[nirrobi]

Dear PBI community and especially PQ specialists. 

I face a situation that I am sure that it's pretty generic but I will try to describe it as I understand it.

I have EDI file (txt format) that contains many lines with the same pattern, e.g.

01xxxx1234567y123456789

And I need to break it into 3  columns according to dictionary/translation table:

From column - column size - column name

1 - 2 - prefix

3 - 11 - container

14 - 10 - BL

Attached link to PBIX file with a sample of data, dictionary table and manual query that shows the expected result. 

https://www.dropbox.com/s/mduxyobbrsn42ax/PQ%20parameter%20Table.pbix?dl=0

Just to make clear, every line in the EDI file is very long and I have many EDI file I need to translate. 

Every EDI file has different dictionary table. 

Any suggestions to automate the process will be much appreciated. 

Regards. 

Nir

[nirrobi · Accepted solution]

I found a solution,

THANK YOU BILL SZYSZ !!

The magic is in this line:

Splited = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByPositions(TranslationTable[From Column], false), TranslationTable[Column Name])

"

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

Code:

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)

Code:

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

"

[Greg_Deckler]

When you say that every file has a different dictionary/translation table, what do you mean exactly? So for some EDI files you would have:

1-5 - Prefix

6-19 - Container

20-26 BL

27-30 Something

Like that?

If that is the case, how many variations of this do you have?

[nirrobi]

Yes, exactly.
Every edi file come with dictionary table,
I want to have two tables that come from different file , 1-edi, 2-translation
The translation table can have many lines and variations