Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ccarpent
Helper IV
Helper IV

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:

 

CCJCACACCT_NAMEPO_REFSuppllerNamePO_LINESDESCRRIPTIONORDERDATEStatusQtyQty RecAMT
ZXC000A00001888886Type G886228894Company A Limited1size vip13-Mar-202022025.37
ZXC000X00001888886Type G886228894Company A Limited2Delivery13-Mar-202021023.75
EFG000X33331885886Type C886232886Company B & Hygiene Supplies1Gel18-Mar-202021131.04
ABC000XUXT0188711Type 0PPP9389Company B Coaches Ltd1travel14-Feb-2020210290
ABC000X00001885889Type APPP9478Client A/C1Conference, ",07-Aug-2020"210150.00 
ABC000X31355888822Type BPPP9256Courier3clothes20-Feb-20192101
ZXC000A9999188592Type HIC778844Picture Ltd1gel08-Jul-202022500885
EFG000X00001885887Type aIC778848SoleTrader Name1project10-Aug-2020210575.20
EFG000X111118888388Type E886348879Stationary are US1keyboard14-Aug-202021140.24
ZXC000A44441885889Type F88633283Stationary are US1gel cleaning10-Aug-2020220059.28
ZXC000A99991885889Type F88634353Stationary are US1mouse17-Aug-202023375.13
ZXC000X00001888886Type GIC778849Plumber1service14-Aug-2020210297.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:

CCJCACACCT_NAMEPO_REFSuppllerNamePO_LINESDESCRRIPTIONORDERDATEStatusQtyQty RecAMT
ZXC000A00001888886Type G886228894Company A Limited1size vip13-Mar-202022025.37
ZXC000X00001888886Type G886228894Company A Limited2Delivery13-Mar-202021023.75
EFG000X33331885886Type C886232886Company B & Hygiene Supplies1Gel18-Mar-202021131.04
ABC000XUXT0188711Type 0PPP9389Company B Coaches Ltd1travel14-Feb-2020210290
ABC000X00001885889Type APPP9478Client A/C1Conference07-Aug-2020210150.00
ABC000X31355888822Type BPPP9256Courier3clothes20-Feb-20192101
ZXC000A9999188592Type HIC778844Picture Ltd1gel08-Jul-202022500885
EFG000X00001885887Type aIC778848SoleTrader Name1project10-Aug-2020210575.20
EFG000X111118888388Type E886348879Stationary are US1keyboard14-Aug-202021140.24
ZXC000A44441885889Type F88633283Stationary are US1gel cleaning10-Aug-2020220059.28
ZXC000A99991885889Type F88634353Stationary are US1mouse17-Aug-202023375.13
ZXC000X00001888886Type GIC778849Plumber1service14-Aug-2020210297.00

Can you offer any help on how I may acheived, would be greatly appriciated?

Many thanks, Chris Carpenter

1 ACCEPTED SOLUTION

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

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

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

Icey
Community Support
Community Support

Hi @ccarpent ,

 

How did you solve the issue? Can you share your solution with us? We are curious.

 

 

Best Regards,

Icey

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
Community Support
Community Support

Hi @ccarpent ,

 

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

Greg_Deckler
Super User
Super User

@ccarpent - 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])


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors