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.
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
Solved! Go to 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
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 :-))
"
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
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 :-))
"
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?
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 | |
100 | |
78 | |
75 | |
50 |
User | Count |
---|---|
144 | |
109 | |
108 | |
87 | |
61 |