Solved: Import of TXT files > columns - Page 2

Willborn · ‎01-29-2016

Hi there!

I'm currently testing Power BI and struggling with the following task. Did not found a solution in the web so far...:

We have an interface delivering .txt files with blank spaces according to the origin field lenght. When I try to import this file (.txt or auto, UTF-8), Power BI Desktop removes the empty text and due to the mixed content I'm not able to get it transformed into the right columns.

An Example:

Text 1 000122 122.22.00 Restic Shipping (Rotterdam) Ltd 122.50 Max Muster

Text 2 000134 122.89.00 Norabosk Gas Terminal NV 50.20 John Doe

Text 3 000458 122.88.01 Tenkay Handelsgesellschaft m.b.H. 1250.25 Jane Smith

Ends up in PBI as:

Text 1 000122 122.22.00 Restic Shipping (Rotterdam) Ltd 122.50 Max Muster

Text 2 000134 122.89.00 Norabosk Gas Terminal NV 50.20 John Doe

Text 3 000458 122.88.01 Tenkay Handelsgesellschaft m.b.H. 1250.25 Jane Smith

With above import, I' cannot split the tex using number of characters, what actually would be the best way as I know the lenght of the exported fields, and the conten may vary.

Does somone can help me? Thanks!!!

Greg_Deckler · ‎01-29-2016

@Willborn - I couldn't get my first thought of columns in source to work for some weird reason. However, I was able to split the column on 33 and make it look right:

let
    Source = Csv.Document(File.Contents("C:\temp\powerbi\Willborn\text2.txt"),null,{0,5,11,22,36,79},null,1252),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}, {"Column3", Int64.Type}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}}),
    #"Split Column by Position" = Table.SplitColumn(#"Changed Type","Column5",Splitter.SplitTextByRepeatedLengths(33),{"Column5.1", "Column5.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Column5.1", type text}, {"Column5.2", type text}})
in
    #"Changed Type1"

So I split it on Position 33 essentially after the initial source step. So the UI way to do it is to edit the query, go to Transform tab, choose column, choose Split Column from Text Column area and in drop down choose "By Position". Then you can enter the number of characters, 33 and you should be good to go.

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!: The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

Import of TXT files > columns

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024