Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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 nrow

    in
    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

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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)
Anonymous's avatar
Anonymous
Not applicable
5 years ago

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