Forum Discussion
Extract date value from column, csv file
Hello,
I am importing a several csv files into Power BI, but it appears the text is occasionally not being formatted ccorrectly.
Exampel Table A: This is how the table looks before I format the row heading types:
| CC | JC | AC | ACCT_NAME | PO_REF | SuppllerName | PO_LINES | DESCRRIPTION | ORDERDATE | Status | Qty | Qty Rec | AMT |
| ZXC000 | A00001 | 888886 | Type G | 886228894 | Company A Limited | 1 | size vip | 13-Mar-2020 | 2 | 2 | 0 | 25.37 |
| ZXC000 | X00001 | 888886 | Type G | 886228894 | Company A Limited | 2 | Delivery | 13-Mar-2020 | 2 | 1 | 0 | 23.75 |
| EFG000 | X33331 | 885886 | Type C | 886232886 | Company B & Hygiene Supplies | 1 | Gel | 18-Mar-2020 | 2 | 1 | 1 | 31.04 |
| ABC000 | XUXT01 | 88711 | Type 0 | PPP9389 | Company B Coaches Ltd | 1 | travel | 14-Feb-2020 | 2 | 1 | 0 | 290 |
| ABC000 | X00001 | 885889 | Type A | PPP9478 | Client A/C | 1 | Conference, ",07-Aug-2020" | 2 | 1 | 0 | 150.00 | |
| ABC000 | X31355 | 888822 | Type B | PPP9256 | Courier | 3 | clothes | 20-Feb-2019 | 2 | 1 | 0 | 1 |
| ZXC000 | A99991 | 88592 | Type H | IC778844 | Picture Ltd | 1 | gel | 08-Jul-2020 | 2 | 250 | 0 | 885 |
| EFG000 | X00001 | 885887 | Type a | IC778848 | SoleTrader Name | 1 | project | 10-Aug-2020 | 2 | 1 | 0 | 575.20 |
| EFG000 | X11111 | 8888388 | Type E | 886348879 | Stationary are US | 1 | keyboard | 14-Aug-2020 | 2 | 1 | 1 | 40.24 |
| ZXC000 | A44441 | 885889 | Type F | 88633283 | Stationary are US | 1 | gel cleaning | 10-Aug-2020 | 2 | 20 | 0 | 59.28 |
| ZXC000 | A99991 | 885889 | Type F | 88634353 | Stationary are US | 1 | mouse | 17-Aug-2020 | 2 | 3 | 3 | 75.13 |
| ZXC000 | X00001 | 888886 | Type G | IC778849 | Plumber | 1 | service | 14-Aug-2020 | 2 | 1 | 0 | 297.00 |
Row 5, the description in this example contains the date value; which is surrounded by a comma and quotation mark ,07-Aug-2020" ; this should be populated in the column named 'ORDERDATE'. The 3 proceeding columns to the right are all moved left by one column. What do notice is the colunm 'AMT' is blank which I could use to identify when this happens but what I dont know is how I can can extract the date value plus move the corresponding values across to their correct columns.
Table B is what it shoud look like:
| CC | JC | AC | ACCT_NAME | PO_REF | SuppllerName | PO_LINES | DESCRRIPTION | ORDERDATE | Status | Qty | Qty Rec | AMT |
| ZXC000 | A00001 | 888886 | Type G | 886228894 | Company A Limited | 1 | size vip | 13-Mar-2020 | 2 | 2 | 0 | 25.37 |
| ZXC000 | X00001 | 888886 | Type G | 886228894 | Company A Limited | 2 | Delivery | 13-Mar-2020 | 2 | 1 | 0 | 23.75 |
| EFG000 | X33331 | 885886 | Type C | 886232886 | Company B & Hygiene Supplies | 1 | Gel | 18-Mar-2020 | 2 | 1 | 1 | 31.04 |
| ABC000 | XUXT01 | 88711 | Type 0 | PPP9389 | Company B Coaches Ltd | 1 | travel | 14-Feb-2020 | 2 | 1 | 0 | 290 |
| ABC000 | X00001 | 885889 | Type A | PPP9478 | Client A/C | 1 | Conference | 07-Aug-2020 | 2 | 1 | 0 | 150.00 |
| ABC000 | X31355 | 888822 | Type B | PPP9256 | Courier | 3 | clothes | 20-Feb-2019 | 2 | 1 | 0 | 1 |
| ZXC000 | A99991 | 88592 | Type H | IC778844 | Picture Ltd | 1 | gel | 08-Jul-2020 | 2 | 250 | 0 | 885 |
| EFG000 | X00001 | 885887 | Type a | IC778848 | SoleTrader Name | 1 | project | 10-Aug-2020 | 2 | 1 | 0 | 575.20 |
| EFG000 | X11111 | 8888388 | Type E | 886348879 | Stationary are US | 1 | keyboard | 14-Aug-2020 | 2 | 1 | 1 | 40.24 |
| ZXC000 | A44441 | 885889 | Type F | 88633283 | Stationary are US | 1 | gel cleaning | 10-Aug-2020 | 2 | 20 | 0 | 59.28 |
| ZXC000 | A99991 | 885889 | Type F | 88634353 | Stationary are US | 1 | mouse | 17-Aug-2020 | 2 | 3 | 3 | 75.13 |
| ZXC000 | X00001 | 888886 | Type G | IC778849 | Plumber | 1 | service | 14-Aug-2020 | 2 | 1 | 0 | 297.00 |
Can you offer any help on how I may acheived, would be greatly appriciated?
Many thanks, Chris Carpenter
Icey
Absolutly, happy to share.
In the end I applied a 'Conditional Column' in PowrQuery; basically if I find this block of text change it to this very simple and manual process. I went back to Rocco's code, trying this segment of is M Language to work on my table which in onthe face of it looks straight forward enough to follow(its amazing what a bit of rest does):
<The code or Source before this, json, I assuems was to just recreate my example table>
tr=Table.TransformRows(ct, each if _[AMT]=null then adjust(_) else _),
adjust=(row)=>
let
des=List.RemoveMatchingItems(Text.SplitAny(row[DESCRRIPTION]," ,"""),{"",null}),
nrow=row&[DESCRRIPTION=des{0}, ORDERDATE=des{1},Status=row[ORDERDATE],Qty=row[Status],#"Qty Rec"=row[Qty],AMT=row[Qty Rec] ]
in nrowin
Table.FromRecords(tr)I dont know what I did the first time around but can seem to get the same results. The source data is actually pdf documents, I may have coverted this to csv/slx first then used Power BI.
Hope this is ok, again appriciate peoples time and effort putting these solutions together.
Many thanks
Chris Carpenter
6 Replies
Anonymous - Try changing your QuoteStyle perhaps. Can you paste the first line of your query from Advanced Editor?
Source = Csv.Document(File.Contents("C:\temp\powerbi\fishfacts.txt"),[Delimiter=":", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None])
trythis
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZRvb5swEMa/isXrhBkbB/OSsDbdVE2R0krRmrxwyDX1xj85EIl9+tnGtAlNq2knnTiMuB/P4zNPT97PdYox9iZeoi840AU3MdPFQ1cDWtiVGSGcx6Gu06qoRdmhBN3LQjaw12vmraP8A+gka3NLp4VQU2K6Epe2Zj6NvO3kDLr+f6jp+hVyeQLVXYEGA5T6EbPQm9uFg1IdPZSdQVMHpaRfHKBztGkxJjN01x0klIBWbV3nEo6OsoDcVPw93yQNfBxafjIfRD+uH5zoKAgGvHmyXC5jyuMLeFqJ7AWO6L4ZrG6UOPXMcPoMuyuaYzwivtmsFccDMnHIMOIGqTWVDUq+pK5VWpXPoKDMYII23gRH06Q9aBrBG29EDBj2LWnEpQFlzG0vIQN37riE9T63SoIyXunM8qp5sd4SbNUF8Zh1OUJJrMNpi18Rd7r4lkYR56EZoKXMmlbBmYkH6yDm0+9tbjUNw8qw4+h+o7m5cDEaUOINZWxcVTk8KLEHhX6IAhyuVtUvyBpzh19tHAljEfMJHjEDE85ByvkAvemHlYb6Q4xBq0Y0siqF6pDQOh9Xru9v6HaVUPt+Wq6ATYbYJ+HI1VDH+4m5dVh9RugnVG0uynIQpSwPVyWTwWQW+4R/vKFX0CFln6GLqj1a06Mxk7rULgf0n39DbmfNZyzzttjZQbU/PFAnmcGHxvYHMTLHYrv9Cw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [CC = _t, JC = _t, AC = _t, ACCT_NAME = _t, PO_REF = _t, SuppllerName = _t, PO_LINES = _t, DESCRRIPTION = _t, ORDERDATE = _t, Status = _t, Qty = _t, #"Qty Rec" = _t, AMT = _t]), ct = Table.TransformColumnTypes(Source,{{"CC", type text}, {"JC", type text}, {"AC", Int64.Type}, {"ACCT_NAME", type text}, {"PO_REF", type text}, {"SuppllerName", type text}, {"PO_LINES", Int64.Type}, {"DESCRRIPTION", type text}, {"ORDERDATE", type text}, {"Status", Int64.Type}, {"Qty", Int64.Type}, {"Qty Rec", Int64.Type}, {"AMT", Int64.Type}}), tr=Table.TransformRows(ct, each if _[AMT]=null then adjust(_) else _), adjust=(row)=> let des=List.RemoveMatchingItems(Text.SplitAny(row[DESCRRIPTION]," ,"""),{"",null}), nrow=row&[DESCRRIPTION=des{0}, ORDERDATE=des{1},Status=row[ORDERDATE],Qty=row[Status],#"Qty Rec"=row[Qty],AMT=row[Qty Rec] ] in nrow in Table.FromRecords(tr)Rocco
Your solution is fantatsic and I appriacte the the time and effect spent compiling this answer however its wasted on me as my level of knowledge is not at this level....not just yet. I will use the other option for now, its on level a better suietd to my understanding(just). Again, I can not empahsis enough how grateful I am your your help on this thanks, cheers.
Chris
Hi Anonymous ,
How did you solve the issue? Can you share your solution with us? We are curious.
Best Regards,
Icey
