Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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
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 @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
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
@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])