Power Query from Text file

[Quiquegl]

Dear Users,

I have this text file imported from a law firm software (Aderant) of about 10k rows. This Text file can be imported in Excel by using Text Import Wizard. The Excel report is displayed in multiple columns. Problem: The report needs a lot of cleanup.

So I open Power Query and import the report from text to clean up the data. Unfortunately, the data shows in a single column and there is no way to trim it.

How can import the file from Power Query so that the report shows in multiple columns? If I got it in multiple columns, I would be able to clean it.

 

[anvg]

Hi
Can you write an example of your text file here for a help you?
Regards,

 

[Quiquegl]

= Table.FromColumns({Lines.FromBinary(File.Contents("X:\temp\Expenses2.txt"),null,null,1252)})

Column1
"Account: 820.00001 Telephone Service (Local calls) Opening Balance: $0.00"
"Office: BE Beijing Opening Balance: $0.00"
"201501 3153793 GI JE 2/3/2015 $7,340.35 Session - 39487 BE Operating Transactions - January 2015 Telephone and network service fee paid to China Telecom for Q4 2014"
"201503 3168444 DR CH 4/1/2015 $4,804.82 Type - CH Number - BOFA29-(460535) Date - 3/9/2015 Name - China Telecom Telephone and network service fee paid to China Telecom for Jan-Feb 2015"
"201504 3176342 DR CH 5/1/2015 $2,444.52 Type - CH Number - BOFA29-(463174) Date - 4/9/2015 Name - China Telecom Telephone and network service fee paid to China Telecom for Mar 2015"
"201505 3183790 DR CH 6/1/2015 $2,436.82 Type - CH Number - BOFA29-(465742) Date - 5/8/2015 Name - China Telecom Telephone and network service fee paid to China Telecom for Apr 2015"
"201506 3190276 DR CH 6/26/2015 $2,440.41 Type - CH Number - BOFA29-(468154) Date - 6/8/2015 Name - China Telecom Telephone and network service fee paid to China Telecom for May 2015"
"201506 3191679 DR CH 7/1/2015 $139.65 Type - CH Number - BOFA29-(468667) Date - 6/29/2015 Name - China Telecom Telephone and network service fee paid to China Telecom for June 2015"
"201507 3200211 DR CH 8/3/2015 $2,397.61 Type - CH Number - BOFA29-(471272) Date - 7/7/2015 Name - China Telecom Telephone and network service fee paid to China Telecom for June 2015"
"201508 3208263 DR CH 9/1/2015 $2,386.22 Type - CH Number - BOFA29-(473994) Date - 8/7/2015 Name - China Telecom Telephone and network service fee paid to China Telecom for July 2015"
"201509 3215230 DR CH 9/28/2015 $2,345.49 Type - CH Number - BOFA29-(476332) Date - 9/7/2015 Name - China Telecom Telephone and network service fee paid to China Telecom for Aug 2015"
"201510 3224771 DR CH 11/2/2015 $2,324.02 Type - CH Number - BOFA29-(479580) Date - 10/12/2015 Name - China Telecom Telephone and network service fee paid to China Telecom for Sep 2015"
"201511 3232424 DR CH 12/1/2015 $2,291.24 Type - CH Number - BOFA29-(481767) Date - 11/6/2015 Name - China Telecom Telephone and network service fee paid to China Telecom for Oct 2015"
"201512 3241530 DR CH 1/4/2016 $2,471.51 Type - CH Number - BOFA29-(485348) Date - 12/8/2015 Name - China Telecom Telephone and network service fee paid to China Telecom for Nov 2015"
"Office: BR Brussels Opening Balance: $0.00"
"201501 3147050 DR CH 1/5/2015 $205.86 Type - CH Number - WFB101-(453048) Date - 1/5/2015 Name - Belgacom Belgacom - Phone Charges - 12/2014 (EUR 169.31 @ 1.2159)"
"201502 3157731 DR CH 2/19/2015 $212.51 Type - CH Number - WFB101-(456702) Date - 2/19/2015 Name - Global Exchange Group Belgacom - Proximus telephone charges 1/-31/15 - BR"
"201503 3161163 DR CH 3/4/2015 $253.76 Type - CH Number - WFB101-(457977) Date - 3/4/2015 Name - Global Exchange Group Belgacom - Proximus telephone charges 1/1-31/15 - BR"
"201503 3161720 DR CH 3/6/2015 $142.68 Type - CH Number - WFB101-(458217) Date - 3/6/2015 Session - 18159 Name - Dombek, Curtis M. Dombek - Annual Fee for Skype (Int'l Calls/BR Office) - 2/11/15"
"201504 3170497 DR CH 4/9/2015 $227.96 Type - CH Number - WFB101-(461199) Date - 4/9/2015 Name - Global Exchange Group Belgacom - Proximus telephone charges 3/2015 - BR"
"201505 3179041 DR CH 5/13/2015 $224.94 Type - CH Number - WFB101-(464080) Date - 5/13/2015 Name - Global Exchange Group Belgacom - Phone Charges - 04/2015 (EUR 191.76 @ 1.1730287)"
"201506 3188132 DR CH 6/17/2015 $245.04 Type - CH Number - WFB101-(467276) Date - 6/17/2015 Name - Global Exchange Group Belgacom - Phone Charges - 04/2015 (EUR 210.23 @ 1.1655689)"
"201507 3191681 DR CH 7/1/2015 $246.54 Type - CH Number - WFB101-(468679) Date - 7/1/2015 Name - Global Exchange Group Belgacom - Phone Charges - 05/2015 (EUR 213.40 @ 1.15531451)"
"201509 3217605 MA JE 10/2/2015 $199.23 ING Banking Transactions Proximus - Telephone - 06/2015 (EUR 176.61 @ .8865) Note: payment sent by SMRH US thru Global Exchange was not received by Proximus (as claimed)"
"201509 3217605 MA JE 10/2/2015 $207.14 ING Banking Transactions Proximus - Telephone - 08/2015 (EUR 183.64 @ .8865)"
"201510 3225595 MA JE 11/4/2015 $175.38 ING Bank Transactions Proximus - Inv. #6532208799 - Telephone - 09/2015 (Euro 156.63 @ .8931)"
"201510 3225595 MA JE 11/4/2015 $175.44 ING Bank Transactions Proximus - Invoice #6527196270 - Phone Charges - 07/2015 (Euro 156 @ .8892)"
"201511 3230013 DR CH 11/20/2015 $365.78 Type - CH Number - BOFA01-(481176) Date - 11/20/2015 Name - Regus Regus - Telephone Calls - 10/06-10/30/15 (Euro 337.31 @ 1.0844)"
"201512 3243194 MA JE 1/11/2016 $294.30 ING Bank Transactions - 12/2015 Proximus - Inv. #6534713193 - Phone Charges - 10/2015 (Euro 276.70 @ .9402)"
"Office: CS Central Services Opening Balance: $0.00"
"201501 3147895 DR CH 1/8/2015 $16,943.80 Type - CH Number - WFB101-(453229) Date - 1/8/2015 Session - 17999 Name - Soundpath Legal Conferencing Soundpath Legal Conferencing- Telephone charges for Billing Period Ending 12/11/2014"
"201503 3161518 DR CH 3/5/2015 $13,663.85 Type - CH Number - WFB101-(458054) Date - 3/5/2015 Session - 18155 Name - Soundpath Legal Conferencing Soundpath Legal Conferencing- Telephone charges for Billing Period Ending 1/11/2015 "

 

[anvg]

Hi
Try with

let
    source = Table.FromColumns({Lines.FromBinary(File.Contents("d:\path\Expenses2.txt"),null,null,1252)}),
    removeQuotes = Table.TransformColumns(source, { {"Column1", each Text.Replace(_, """","")} }),
    dataRows = Table.SelectRows(removeQuotes, each not (Text.StartsWith([Column1],"Account:") or Text.StartsWith([Column1],"Office:"))),
    toColumns = Table.SplitColumn(dataRows,"Column1",Splitter.SplitTextByDelimiter(" "),36)
in
    toColumns

Regards,

 

[Quiquegl]

Thank you so much anvg.

The problem for me is that I am not an expert of M Language so I don't even know where and when to paste your code. My expertise of Power Query is basically about using the menus available (unpivot, transform, split, etc.)

Also, just FYI when I imported the same file with only 2 periods instead of 12, the data was imported into Power Query successfully in different columns. If there was a way to import the data from text in different columns without using M code.