Dùng Query tách chuỗi trong Microsoft office 2016 (1 người xem)

[hichlehg]

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.

 

[ptm0412]

Thử sửa lại step tạo ngày:

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

 

[hichlehg]

Dạ, con cảm ơn chú!

 

[hichlehg]

Thử sửa lại step tạo ngày:

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

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ú.
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"

 

[ptm0412]

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.

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:

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"

Nhưng trong đoạn chú gửi con thấy có dùng hàm Text.Middle(),

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

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:

    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),

 

[hocexcel_1991]

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.

Bạn dùng M code sau xem đúng chưa

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"

 

[hichlehg]

Nếu advanced Editor thì chép nguyên đoạn code:

Bạn dùng M code sau xem đúng chưa

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.

bằng 3 dòng:

Con thay thì nó báo lỗi: Expression.Error: A cyclic reference was encountered during evaluation.
Code sau khi con thay:

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"

 

[ptm0412]

Con thay thì nó báo lỗi: Expression.Error: A cyclic reference was encountered during evaluation.

   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),

Sửa Split2 thành Split3 chỗ đỏ đỏ

 

[hichlehg]

Sửa Split2 thành Split3 chỗ đỏ đỏ

Dạ, con làm được rồi. con cảm ơn chú nhiều!

 

[yeuquai_2122]

Thử sửa lại step tạo ngày:

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

code này thả vào đâu trong power query đấy ạ? sau khi add colume mới hay sửa trên chính colume cũ ạ?