cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
nirrobi Helper IV
Helper IV

Convert txt file according to translation table

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

1 ACCEPTED SOLUTION

Accepted Solutions
nirrobi Helper IV
Helper IV

Re: Convert txt file according to translation table

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

"

View solution in original post

3 REPLIES 3
Super User IV
Super User IV

Re: Convert txt file according to translation table

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?


---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

nirrobi Helper IV
Helper IV

Re: Convert txt file according to translation table

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
nirrobi Helper IV
Helper IV

Re: Convert txt file according to translation table

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

"

View solution in original post

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors