viết công thức trong power query

[Hoale85]

Em nhờ các anh / chị trên diễn đàn hướng dẫn em viết công thức trong power query ở sheet1. Em đã viết bằng excel trên file. Em cảm ơn ạ

 

[Hoale85]

Nhờ các bác hướng dẫn giúp em với ạ. Em cảm ơn ạ

 

[hocexcel_1991]

Em nhờ các anh / chị trên diễn đàn hướng dẫn em viết công thức trong power query ở sheet1. Em đã viết bằng excel trên file. Em cảm ơn ạ

Bạn dùng M code sau nhé.

let
  Source = Excel.CurrentWorkbook(){[Name = "Table3"]}[Content],
  ketqua = List.Combine(
    Table.Group(
      Source,
      {"Mã KH"},
      {
        {
          "Sum",
          each
            let
              lts = [Tổng tiền]
            in
              List.Transform(
                {0 .. List.Count(lts) - 1},
                each try lts{_} + lts{_ - 1} otherwise lts{0}
              )
        }
      }
    )[Sum]
  )
in
  ketqua

 

[Hoale85]

Bạn dùng M code sau nhé.

let
  Source = Excel.CurrentWorkbook(){[Name = "Table3"]}[Content],
  ketqua = List.Combine(
    Table.Group(
      Source,
      {"Mã KH"},
      {
        {
          "Sum",
          each
            let
              lts = [Tổng tiền]
            in
              List.Transform(
                {0 .. List.Count(lts) - 1},
                each try lts{_} + lts{_ - 1} otherwise lts{0}
              )
        }
      }
    )[Sum]
  )
in
  ketqua

View attachment 303032

đưa code này vào như vậy à anh. hay là đưa code này vào đâu ạ. Em thêm cột và đưa vào thì đang lỗi ạ.

Bài đã được tự động gộp: 9/8/24

Bạn dùng M code sau nhé.

let
  Source = Excel.CurrentWorkbook(){[Name = "Table3"]}[Content],
  ketqua = List.Combine(
    Table.Group(
      Source,
      {"Mã KH"},
      {
        {
          "Sum",
          each
            let
              lts = [Tổng tiền]
            in
              List.Transform(
                {0 .. List.Count(lts) - 1},
                each try lts{_} + lts{_ - 1} otherwise lts{0}
              )
        }
      }
    )[Sum]
  )
in
  ketqua

View attachment 303032

Em làm được rồi ạ, em cảm ơn bác ạ.

 

[Hoale85]

Bạn dùng M code sau nhé.

let
  Source = Excel.CurrentWorkbook(){[Name = "Table3"]}[Content],
  ketqua = List.Combine(
    Table.Group(
      Source,
      {"Mã KH"},
      {
        {
          "Sum",
          each
            let
              lts = [Tổng tiền]
            in
              List.Transform(
                {0 .. List.Count(lts) - 1},
                each try lts{_} + lts{_ - 1} otherwise lts{0}
              )
        }
      }
    )[Sum]
  )
in
  ketqua

View attachment 303032

Anh ơi, sao sang bảng khác nó lại báo lỗi như này anh nhỉ? Em ko biết nó báo lỗi thế nào anh ạ. A giúp em với ạ

Bài đã được tự động gộp: 9/8/24

Bạn dùng M code sau nhé.

let
  Source = Excel.CurrentWorkbook(){[Name = "Table3"]}[Content],
  ketqua = List.Combine(
    Table.Group(
      Source,
      {"Mã KH"},
      {
        {
          "Sum",
          each
            let
              lts = [Tổng tiền]
            in
              List.Transform(
                {0 .. List.Count(lts) - 1},
                each try lts{_} + lts{_ - 1} otherwise lts{0}
              )
        }
      }
    )[Sum]
  )
in
  ketqua

View attachment 303032

Bài đã được tự động gộp: 9/8/24

công thức hình như lỗi hay sao bác ạ

 

[hocexcel_1991]

View attachment 303034View attachment 303035
Anh ơi, sao sang bảng khác nó lại báo lỗi như này anh nhỉ? Em ko biết nó báo lỗi thế nào anh ạ. A giúp em với ạ

Bài đã được tự động gộp: 9/8/24

Nếu bạn muốn load cả bảng thì dùng mcode sau:

// Table3
let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    ketqua = Table.Combine(Table.Group(Source, {"Mã KH"}, {{"Sum", each let lts = Table.ToColumns(_) in Table.FromColumns(lts&{List.Transform({0..List.Count(lts{1})-1}, each try lts{1}{_}+lts{1}{_-1} otherwise lts{1}{0})},Table.ColumnNames(_)&{"Run"})}})[Sum])
in
    ketqua

 

[Hoale85]

Nếu bạn muốn load cả bảng thì dùng mcode sau:

// Table3
let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    ketqua = Table.Combine(Table.Group(Source, {"Mã KH"}, {{"Sum", each let lts = Table.ToColumns(_) in Table.FromColumns(lts&{List.Transform({0..List.Count(lts{1})-1}, each try lts{1}{_}+lts{1}{_-1} otherwise lts{1}{0})},Table.ColumnNames(_)&{"Run"})}})[Sum])
in
    ketqua

Nếu số lần thứ 3, 4... công thức sẽ chạy sai anh ạ. Chỉ đúng với số lần 1,2 anh ạ. Anh sửa lại giúp em với anh. Em cảm ơn ạ

 

[Hoale85]

Các bác giúp em với ạ

 

[ptm0412]

Các bác giúp em với ạ

Cột D và E của bạn có công thức có vẻ đang sai nếu như đang muốn tính lũy kế.
Tính lũy kế:
D2 =IF(C2=C1,D1+B2,B2)
E2 =COUNTIF($C$2:C2,C2)

 

[Hoale85]

Cột D và E của bạn có công thức có vẻ đang sai nếu như đang muốn tính lũy kế.
Tính lũy kế:
D2 =IF(C2=C1,D1+B2,B2)
E2 =COUNTIF($C$2:C2,C2)

Mình tính lũy kế ở cột B mà bạn, cột C là số lần xuất hiện. Cột D ko liên quan

 

[Hoale85]

Cột D và E của bạn có công thức có vẻ đang sai nếu như đang muốn tính lũy kế.
Tính lũy kế:
D2 =IF(C2=C1,D1+B2,B2)
E2 =COUNTIF($C$2:C2,C2)

Ý của mình cột E chạy code nó đang tính sai í: chỉ đúng khi số lần lập lại bằng <=2, còn lớn hơn thì không đúng ạ.

 

[ptm0412]

Ý của mình cột E chạy code nó đang tính sai í: chỉ đúng khi số lần lập lại bằng <=2, còn lớn hơn thì không đúng ạ.

Công thức của bạn sai trước. Rồi PQ chưa biết đúng sai nhưng bạn đang đối chiếu với cột sai của bạn.
Ngoài ra bạn đã tính lại đúng trong chủ đề mới (giống công thức của tôi)

 

[Hoale85]

Công thức của bạn sai trước. Rồi PQ chưa biết đúng sai nhưng bạn đang đối chiếu với cột sai của bạn.

Mình gửi lại file nhé. Mình nhờ bạn viết code M trong power query hộ mình với

 

[ptm0412]

Code lũy kế:

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    Sort = Table.Sort(Source,{{"Mã KH", Order.Ascending}, {"tiền", Order.Ascending}}),
    Types = Table.TransformColumnTypes(Sort,{{"tiền", type number}}),
    Index = Table.AddIndexColumn(Types, "STT", 1, 1, Int64.Type),
    Custom2 = Table.AddColumn(Index, "Ct2", each Table.SelectColumns(Table.FirstN(Index,[STT]),{"Mã KH", "tiền"})),
    Custom3 = List.Transform (Custom2[Ct2], each
        let 
            a = Table.LastN(_, 1)[Mã KH]{0},
            Filter = Table.SelectRows(_, each [Mã KH] = a),
            SumTien = List.Sum(Filter[tiền])
        in SumTien),
    Result = Table.AddColumn(Table.SelectColumns(Custom2,{"STT","Mã KH", "Mã khách hàng", "tiền"}), "Luyke", each Custom3{[STT] - 1})
in
   Result

 

[Hoale85]

Code lũy kế:

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    Sort = Table.Sort(Source,{{"Mã KH", Order.Ascending}, {"tiền", Order.Ascending}}),
    Types = Table.TransformColumnTypes(Sort,{{"tiền", type number}}),
    Index = Table.AddIndexColumn(Types, "STT", 1, 1, Int64.Type),
    Custom2 = Table.AddColumn(Index, "Ct2", each Table.SelectColumns(Table.FirstN(Index,[STT]),{"Mã KH", "tiền"})),
    Custom3 = List.Transform (Custom2[Ct2], each
        let
            a = Table.LastN(_, 1)[Mã KH]{0},
            Filter = Table.SelectRows(_, each [Mã KH] = a),
            SumTien = List.Sum(Filter[tiền])
        in SumTien),
    Result = Table.AddColumn(Table.SelectColumns(Custom2,{"STT","Mã KH", "Mã khách hàng", "tiền"}), "Luyke", each Custom3{[STT] - 1})
in
   Result

View attachment 303100

Em cảm ơn bác ạ!

 

[Hoale85]

Code lũy kế:

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    Sort = Table.Sort(Source,{{"Mã KH", Order.Ascending}, {"tiền", Order.Ascending}}),
    Types = Table.TransformColumnTypes(Sort,{{"tiền", type number}}),
    Index = Table.AddIndexColumn(Types, "STT", 1, 1, Int64.Type),
    Custom2 = Table.AddColumn(Index, "Ct2", each Table.SelectColumns(Table.FirstN(Index,[STT]),{"Mã KH", "tiền"})),
    Custom3 = List.Transform (Custom2[Ct2], each
        let
            a = Table.LastN(_, 1)[Mã KH]{0},
            Filter = Table.SelectRows(_, each [Mã KH] = a),
            SumTien = List.Sum(Filter[tiền])
        in SumTien),
    Result = Table.AddColumn(Table.SelectColumns(Custom2,{"STT","Mã KH", "Mã khách hàng", "tiền"}), "Luyke", each Custom3{[STT] - 1})
in
   Result

View attachment 303100

Anh ơi, anh có thể giải thích giúp em chút về code trên được không anh. Để em hiểu logic code đó hơn ạ. Em cảm ơn anh ạ

 

[ptm0412]

Anh ơi, anh có thể giải thích giúp em chút về code trên được không anh. Để em hiểu logic code đó hơn ạ. Em cảm ơn anh ạ

Bạn xem kết quả từng step cũng thấy.

- Sort = Table.Sort(Source,{{"Mã KH", Order.Ascending}, {"tiền", Order.Ascending}}): Sắp thứ tự theo mã tăng dần và số tiền tăng dần

- Types = Table.TransformColumnTypes(Sort,{{"tiền", type number}}): Đổi kiểu cột tiền để có thể tính toán (mặc định any nhưng không tính toán được)

- Index = Table.AddIndexColumn(Types, "STT", 1, 1, Int64.Type): Tạo cột index bắt đầu bằng 1. Sẽ dùng cột này cho bước kế

- Custom2 = Table.AddColumn(Index, "Ct2", each Table.SelectColumns(Table.FirstN(Index,[STT]),{"Mã KH", "tiền"})): Tạo cột Ct2 với giá trị mỗi dòng là lấy n dòng đầu của bảng, với n là STT. Kết quả là các bảng có số dòng tăng dần

- Custom3 = List.Transform (Custom2[Ct2], each Khởi tạo vòng lặp
let
a = Table.LastN(_, 1)[Mã KH]{0}: Với mỗi dòng của cột Ct2 là 1 bảng, lọc lấy dòng cuối. Dòng cuối do sắp xếp tăng dần nên có mã KH cuối, lấy giá trị mã KH đó.
Filter = Table.SelectRows(_, each [Mã KH] = a), Lọc từng bảng của từng dòng theo giá trị cuối của bảng vừa tìm được, loại các mã KH khác ra.
SumTien = List.Sum(Filter[tiền]) tính tổng cột tiền của từng bảng
in SumTien),
Result = Table.AddColumn(Table.SelectColumns(Custom2,{"STT","Mã KH", "Mã khách hàng", "tiền"}), "Luyke", each Custom3{[STT] - 1}): Lấy 4 cột của bước Custom2, và thêm 1 cột Luyke, giá trị cột Luyke là kết quả tính Sum của bước Custom3
Cách xem kết quả step custom2: Ở step custom2, click vào 1 ô của cột Ct2 (là 1 table), xem table hiện ra ở dưới. Trong hình là chọn dòng 5, Table có 5 dòng.

 

[DeTong]

Step by step...

- Sort = Table.Sort(Source,{{"Mã KH", Order.Ascending}, {"tiền", Order.Ascending}}): Sắp thứ tự theo mã tăng dần và số tiền tăng dần
...
Result = Table.AddColumn(Table.SelectColumns(Custom2,{"STT","Mã KH", "Mã khách hàng", "tiền"}), "Luyke", each Custom3{[STT] - 1}): Lấy 4 cột của bước Custom2, và thêm 1 cột Luyke, giá trị cột Luyke là kết quả tính Sum của bước Custom3

Qúa rõ ràng và bài bản...