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

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

Icey's avatar
Icey
Community Support
5 years ago

Hi Anonymous ,

 

Thanks for your sharing! Learned a lot.😀

 

In addition, it is suggested to accept your above reply or other replies making sense as solution to your question so that people who may have the same question can get the solution directly. Your contribution is highly appreciated.

 

 

Best Regards,

Icey