Seek help on load txt file to PBI

[Paulyeo11]

Hi All

My AR Raw data :-

https://www.dropbox.com/s/vljjghrz4dpehiy/AR_TS_10.txt?dl=0

The raw data format as I below  :-

After i load into PBI , 3 and 4 column combine with company field . those i mark red color box is having trouble :-

[PhilipTreacy · Accepted solution]

Hi @Paulyeo11 

You need to change the column numbers on the fixed width import dialog.

This query will do the job

let
    Source = Csv.Document(File.Contents("D:\temp\AR_TS_10.txt"),8,{0, 9, 68,72,99, 119, 126, 161},null,1252)
in
    Source

Change the file location.  I've loaded it form hard disk, assume you are doing the same?

Phil 

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

[amitchandak]

@Paulyeo11 , Try delimiter as Tab \t and check-in place of fixed-width while loading. 

[Paulyeo11]

Hi Amit

I cannot find it. Can you pls share how to do it ? ( some time back i have the same issue , some one is able to do it  , i ask him how he do it , he no reply my post )

[PhilipTreacy · Accepted solution]

Hi @Paulyeo11 

You need to change the column numbers on the fixed width import dialog.

This query will do the job

let
    Source = Csv.Document(File.Contents("D:\temp\AR_TS_10.txt"),8,{0, 9, 68,72,99, 119, 126, 161},null,1252)
in
    Source

Change the file location.  I've loaded it form hard disk, assume you are doing the same?

Phil 

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

[Paulyeo11]

Hi Philip

Yes these are the script i need to learn , can you pls share with me how you do it ?

may be you can share the PBI file with me

Paul

[PhilipTreacy · Accepted solution]

Hi @Paulyeo11 

Here's the PBIX file

All I did was open the text file in VS Code and count the column numbers so I knew what numbers to insert in the import dialog.

The numbers {0, 9, 68, 72, 99, 119, 126, 161} indicate the column numbers where PBI creates a new column.

Phil

[Paulyeo11]

Hi Philip

Thank you so much for the file attachment , and now i finally understand how does it work , and i don't need to convert all my Text file to CSV file. i owe you a beer.

Paul Yeo