cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Prodigy
Post Prodigy

Seek help on load txt file to PBI

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

Paulyeo11_0-1603862093015.png

 

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

Paulyeo11_1-1603862270220.png

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted

Hi @Paulyeo11 

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

csv-fixed-width.png

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.

View solution in original post

Highlighted

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

View solution in original post

6 REPLIES 6
Highlighted
Super User IV
Super User IV

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted

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 )

 

Paulyeo11_0-1603864703158.png

 

Highlighted

Hi @Paulyeo11 

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

csv-fixed-width.png

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.

View solution in original post

Highlighted

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

Highlighted

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

View solution in original post

Highlighted

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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors