Dùng Query tách chuỗi trong Microsoft office 2016

Liên hệ QC
Tôi tuân thủ nội quy khi đăng bài
Có
H

hichlehg

Thành viên hoạt động
Tham gia
22/6/09
Bài viết
107
Được thích
25
Chào anh/chị/em trong nhóm. Trước đây em có làm file theo dõi hóa đơn điện tử bằng excel (Office 2021), giờ em qua công ty mới thì sử dụng office 2016 nên nó báo lỗi, em kiểm tra thì Office 2016 không có hàm Text.Middle(). Nhờ ACE xem giúp mình với.
Mình có dữ liệu HD_1C23TYY_230925_00000045-NHAN.pdf. Trong đó 230925 mình dùng hàm Text.Middle() để tách và xử lý thành ngày 25/09/2023.
 

File đính kèm

  • TONG HOP LUU TRU HOA DON.xlsx
    53.1 KB · Đọc: 10
Sắp xếp theo thời gian sắp xếp theo bầu chọn
Thử sửa lại step tạo ngày:
Mã: 
Table.AddColumn(#"Renamed Columns2", "Ngày HĐ", each #date(2000 + 
Number.From( Text.Start([TextDate],2)), 
Number.From(Text.Middle([TextDate],2,2)), 
Number.From(Text.End([TextDate],2))), type date)

Sau đó không cần change date local
 
Upvote 0 Downvote
ptm0412 đã viết:
Thử sửa lại step tạo ngày:
Mã: 
Table.AddColumn(#"Renamed Columns2", "Ngày HĐ", each #date(2000 +
Number.From( Text.Start([TextDate],2)),
Number.From(Text.Middle([TextDate],2,2)),
Number.From(Text.End([TextDate],2))), type date)

Sau đó không cần change date local
Nhấp chuột vào đây để mở rộng...
Chú ơi, con không rành về code nên không biết thêm đoạn chủ gửi vào đâu cho đúng. Nhưng trong đoạn chú gửi con thấy có dùng hàm Text.Middle(), mà con đang dùng Office 2016 thì nó không có hàm đó, Nó báo lỗi như hình bên dưới á chú.1699332358497.png
Code file excel con gửi đính kèm bài 1:
let
Source = Excel.CurrentWorkbook(){[Name="DATA"]}[Content],
#"Removed Other Columns" = Table.SelectColumns(Source,{"Name", "Folder Path"}),
#"Duplicated Column" = Table.DuplicateColumn(#"Removed Other Columns", "Name", "Name - Copy"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "Name - Copy", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Name - Copy.1", "Name - Copy.2", "Name - Copy.3", "Name - Copy.4"}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Name - Copy.4", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Name - Copy.4.1", "Name - Copy.4.2"}),
#"Added Custom" = Table.AddColumn(#"Split Column by Delimiter1", "Ngày HĐ", each "20"&Text.Middle([#"Name - Copy.3"],0,2)&"/"&Text.Middle([#"Name - Copy.3"],2,2)&"/"&Text.Middle([#"Name - Copy.3"],4,2)),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Đường dẫn", each [Folder Path]&[Name]),
#"Renamed Columns" = Table.RenameColumns(#"Added Custom1",{{"Name - Copy.2", "Ký hiệu"}, {"Name - Copy.4.1", "Số Hóa đơn"}, {"Ngày HĐ", "Ngày Hóa đơn"}}),
#"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Ký hiệu", "Số Hóa đơn", "Ngày Hóa đơn", "Đường dẫn", "Name", "Folder Path", "Name - Copy.1", "Name - Copy.3", "Name - Copy.4.2"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Reordered Columns",{"Ký hiệu", "Số Hóa đơn", "Ngày Hóa đơn", "Đường dẫn"}),
#"Sorted Rows" = Table.Sort(#"Removed Other Columns1",{{"Số Hóa đơn", Order.Ascending}, {"Đường dẫn", Order.Ascending}}),
#"Changed Type with Locale" = Table.TransformColumnTypes(#"Sorted Rows", {{"Ngày Hóa đơn", type date}}, "en-US"),
#"Split Column by Delimiter2" = Table.SplitColumn(#"Changed Type with Locale", "Số Hóa đơn", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Số Hóa đơn.1", "Số Hóa đơn.2"}),
#"Renamed Columns1" = Table.RenameColumns(#"Split Column by Delimiter2",{{"Số Hóa đơn.1", "Số Hóa đơn"}, {"Số Hóa đơn.2", "Nhân viên Sale"}}),
#"Reordered Columns1" = Table.ReorderColumns(#"Renamed Columns1",{"Ký hiệu", "Số Hóa đơn", "Ngày Hóa đơn", "Nhân viên Sale", "Đường dẫn"})
in
#"Reordered Columns1"
 
Upvote 0 Downvote
hichlehg đã viết:
Chú ơi, con không rành về code nên không biết thêm đoạn chủ gửi vào đâu cho đúng.
Nhấp chuột vào đây để mở rộng...
Nếu sửa step thì thay trong thanh công thức
Nếu advanced Editor thì chép nguyên đoạn code:
Mã: 
let
    Source = Excel.CurrentWorkbook(){[Name="DATA"]}[Content],
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Name", "Folder Path"}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Removed Other Columns", "Name", "Name - Copy"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "Name - Copy", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv),
{"Name - Copy.1", "Name - Copy.2", "Name - Copy.3", "Name - Copy.4"}),
    Split2 = Table.SplitColumn(#"Split Column by Delimiter", "Name - Copy.4", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv),
{"Name - Copy.4.1", "Name - Copy.4.2"}),
    #"Renamed Columns2" = Table.RenameColumns(Split2,{{"Name - Copy.3", "TextDate"}}),


  AddDate = Table.AddColumn(#"Renamed Columns2", "Ngày HĐ", each #date(2000 +
Number.From( Text.Start([TextDate],2)),
Number.From(Text.Middle([TextDate],2,2)),
Number.From(Text.End([TextDate],2))),type date),
    #"Added Custom1" = Table.AddColumn(AddDate, "Đường dẫn", each [Folder Path]&[Name]),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom1",{{"Name - Copy.2", "Ký hiệu"}, {"Name - Copy.4.1", "Số Hóa đơn"}, {"Ngày HĐ", "Ngày Hóa đơn"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Ký hiệu", "Số Hóa đơn", "Ngày Hóa đơn", "Đường dẫn", "Name",
 "Folder Path", "Name - Copy.1", "TextDate", "Name - Copy.4.2"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Reordered Columns",{"Ký hiệu", "Số Hóa đơn", "Ngày Hóa đơn", "Đường dẫn"}),
    #"Sorted Rows" = Table.Sort(#"Removed Other Columns1",{{"Số Hóa đơn", Order.Ascending}, {"Đường dẫn", Order.Ascending}}),
    //#"Changed Type with Locale" = Table.TransformColumnTypes(#"Sorted Rows", {{"Ngày Hóa đơn", type date}}, "en-US"),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Sorted Rows", "Số Hóa đơn", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv),
 {"Số Hóa đơn.1", "Số Hóa đơn.2"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Split Column by Delimiter2",{{"Số Hóa đơn.1", "Số Hóa đơn"}, {"Số Hóa đơn.2", "Nhân viên Sale"}})
   
in
    #"Renamed Columns1"
hichlehg đã viết:
Nhưng trong đoạn chú gửi con thấy có dùng hàm Text.Middle(),
Nhấp chuột vào đây để mở rộng...
Cái hình thông báo lỗi cũ xì của dòng lệnh 3 Text.Middle chứ đâu phải thông báo lỗi mới?
Nếu vẫn lỗi Text.Middle thì thay dòng
Mã: 
AddDate = Table.AddColumn(#"Added Custom", "Ngày HĐ", each #date(2000 + Number.From( Text.Start([TextDate],2)),Number.From(Text.Middle([TextDate],2,2)),Number.From(Text.End([TextDate],2))),type date),
bằng 3 dòng:
Mã: 
    Split2 = Table.SplitColumn(#"Renamed Columns2", "TextDate", Splitter.SplitTextByRepeatedLengths(2), {"TextDate.1", "TextDate.2", "TextDate.3"}),
    Types = Table.TransformColumnTypes(Split2,{{"TextDate.1", Int64.Type}, {"TextDate.2", Int64.Type}, {"TextDate.3", Int64.Type}}),
    AddDate= Table.AddColumn(Types, "Ngày HĐ", each #date(2000 + [TextDate.1],[TextDate.2],[TextDate.3]),type date),
 
Lần chỉnh sửa cuối:
Upvote 0 Downvote
hichlehg đã viết:
Chào anh/chị/em trong nhóm. Trước đây em có làm file theo dõi hóa đơn điện tử bằng excel (Office 2021), giờ em qua công ty mới thì sử dụng office 2016 nên nó báo lỗi, em kiểm tra thì Office 2016 không có hàm Text.Middle(). Nhờ ACE xem giúp mình với.
Mình có dữ liệu HD_1C23TYY_230925_00000045-NHAN.pdf. Trong đó 230925 mình dùng hàm Text.Middle() để tách và xử lý thành ngày 25/09/2023.
Nhấp chuột vào đây để mở rộng...
Bạn dùng M code sau xem đúng chưa
Mã: 
let
    Source = Excel.CurrentWorkbook(){[Name="DATA"]}[Content],
    Custom1 = #table({"Ký hiệu","Số hóa đơn","Nhân viên Sale","Ngày hóa đơn","Đường dẫn"}, List.Transform(Table.ToRows(Source), (x)=> let t= Text.SplitAny(x{1},"-_") in {t{1}}&{t{3}}&{Text.Split(t{4},"."){0}}&{Date.FromText(t{2},[Format="yyMMdd"])}&{x{6}&x{1}})),
    #"Sorted Rows" = Table.Sort(Custom1,{{"Ngày hóa đơn", Order.Ascending}, {"Đường dẫn", Order.Ascending}})
in
    #"Sorted Rows"

1699339449304.png
 
Upvote 0 Downvote
ptm0412 đã viết:
Nếu advanced Editor thì chép nguyên đoạn code:
Nhấp chuột vào đây để mở rộng...
hocexcel_1991 đã viết:
Bạn dùng M code sau xem đúng chưa
Nhấp chuột vào đây để mở rộng...
Con cảm ơn chú @ptm0412 và bạn @hocexcel_1991 đã hỗ code rất thú vị. Nhưng con làm mà cột ngày lỗi Error, con thử đổi change date local mà không hết lỗi.
ptm0412 đã viết:
bằng 3 dòng:
Nhấp chuột vào đây để mở rộng...
Con thay thì nó báo lỗi: Expression.Error: A cyclic reference was encountered during evaluation.
Code sau khi con thay:

Mã: 
let
    Source = Excel.CurrentWorkbook(){[Name="DATA"]}[Content],
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Name", "Folder Path"}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Removed Other Columns", "Name", "Name - Copy"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "Name - Copy", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv),
{"Name - Copy.1", "Name - Copy.2", "Name - Copy.3", "Name - Copy.4"}),
    Split2 = Table.SplitColumn(#"Split Column by Delimiter", "Name - Copy.4", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv),
{"Name - Copy.4.1", "Name - Copy.4.2"}),
    #"Renamed Columns2" = Table.RenameColumns(Split2,{{"Name - Copy.3", "TextDate"}}),


   Split2 = Table.SplitColumn(#"Renamed Columns2", "TextDate", Splitter.SplitTextByRepeatedLengths(2), {"TextDate.1", "TextDate.2", "TextDate.3"}),
    Types = Table.TransformColumnTypes(Split2,{{"TextDate.1", Int64.Type}, {"TextDate.2", Int64.Type}, {"TextDate.3", Int64.Type}}),
    AddDate= Table.AddColumn(Types, "Ngày HĐ", each #date(2000 + [TextDate.1],[TextDate.2],[TextDate.3]),type date),
    #"Added Custom1" = Table.AddColumn(AddDate, "Đường dẫn", each [Folder Path]&[Name]),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom1",{{"Name - Copy.2", "Ký hiệu"}, {"Name - Copy.4.1", "Số Hóa đơn"}, {"Ngày HĐ", "Ngày Hóa đơn"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Ký hiệu", "Số Hóa đơn", "Ngày Hóa đơn", "Đường dẫn", "Name",
 "Folder Path", "Name - Copy.1", "TextDate", "Name - Copy.4.2"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Reordered Columns",{"Ký hiệu", "Số Hóa đơn", "Ngày Hóa đơn", "Đường dẫn"}),
    #"Sorted Rows" = Table.Sort(#"Removed Other Columns1",{{"Số Hóa đơn", Order.Ascending}, {"Đường dẫn", Order.Ascending}}),
    //#"Changed Type with Locale" = Table.TransformColumnTypes(#"Sorted Rows", {{"Ngày Hóa đơn", type date}}, "en-US"),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Sorted Rows", "Số Hóa đơn", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv),
 {"Số Hóa đơn.1", "Số Hóa đơn.2"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Split Column by Delimiter2",{{"Số Hóa đơn.1", "Số Hóa đơn"}, {"Số Hóa đơn.2", "Nhân viên Sale"}})
 
in
    #"Renamed Columns1"
 
Upvote 0 Downvote
hichlehg đã viết:
Con thay thì nó báo lỗi: Expression.Error: A cyclic reference was encountered during evaluation.
Rich (BB code): 
   Split2 = Table.SplitColumn(#"Renamed Columns2", "TextDate", Splitter.SplitTextByRepeatedLengths(2), {"TextDate.1", "TextDate.2", "TextDate.3"}),
    Types = Table.TransformColumnTypes(Split2,{{"TextDate.1", Int64.Type}, {"TextDate.2", Int64.Type}, {"TextDate.3", Int64.Type}}),
    AddDate= Table.AddColumn(Types, "Ngày HĐ", each #date(2000 + [TextDate.1],[TextDate.2],[TextDate.3]),type date),
Nhấp chuột vào đây để mở rộng...
Sửa Split2 thành Split3 chỗ đỏ đỏ
 
Upvote 0 Downvote
Bạn phải đăng nhập hoặc đăng ký để trả lời bài viết tại đây.
Web KT

Bài viết mới nhất

S
Nhờ giúp đỡ code in toàn bộ danh sách có điều kiện
Trả lời
8
Đọc
177
S.Ha
S
S
  • Question
Không lấy đủ dữ liệu bằng kết nối ADO
Trả lời
7
Đọc
187
shnhatha221108
S
Bich2B
lỗi định dạng ngày không đọc được công thức excel
Trả lời
8
Đọc
141
Bich2B
Bich2B
M
Bảng Pivot ko tự update theo dữ liệu và còn cộng thiếu số liệu.
Trả lời
4
Đọc
27
Hoàng Trọng Nghĩa
Hoàng Trọng Nghĩa
hung2412
  • Question
Có Code VBA nào để chỉnh Short date trong Control Panel không? (Ví dụ: Chỉnh dd/mm/yy => dd/mm/yyyy)
Trả lời
4
Đọc
85
hoangtuan.net
hoangtuan.net
Xem thêm…

Facebook

Giaiphapexcel.com

Group

DIỄN ĐÀN GIẢI PHÁP EXCEL Group 1
DIỄN ĐÀN GIẢI PHÁP EXCEL Group 2

Thành viên có số lượng bài viết cao nhất tháng

Thành viên có điểm tương tác cao nhất tháng

Back
Top Bottom