- Tham gia
- 4/11/07
- Bài viết
- 13,804
- Được thích
- 36,315
- Donate (Momo)
- Giới tính
- Nam
- Nghề nghiệp
- Consultant
Trong power query nếu có 1 loại số liệu đang thiết kế dạng nhiều cột, muốn chuyển thành dòng chỉ cần Unpivot table các cột đó.
Trường hợp 2 loại số liệu như số lượng/ thành tiền, doanh thu/chi phí, mỗi nhóm là 2, 3 cột số liệu nối tiếp nhau thì việc unpivot trở nên khó khăn.
Với code sau đây dùng vòng lặp để Select Column khiến cho code nhẹ nhàng hơn rất nhiều. FIle đính kèm có dữ liệu tồn của 12 tháng, mỗi tháng 3 cột, tổng 36 cột.
Trường hợp 1: Tên cột có quy luật như Ma01, SL01, TT01, Ma02, SL02, TT02, ... Ma12, SL12, TT12:
Dùng cấu trúc tên cột để select đúng tên cột, ví dụ "Ma" & n với n từ 1 đến 12 và định dạng "00", tương tự là SL và TT
Trường hợp 2: Tên cột không có quy luật nhưng vẫn theo thứ tự mỗi tháng 3 cột, tuần tự đúng ý nghĩa dữ liệu các cột là Ma, SL và TT dù tên không quy luật (ví dụ Mã1, Ma02, Ma3, Mã 4)
Trường hợp này lấy 1 List tên cột (36 cột), dùng công thức biến đổi biến i (từ 1 đến 12) thành những thứ tự tương ứng từ 1 đến 36, dùng để select.
Code trường hợp 2 này có thể tùy biến khi chưa biết số tháng đã có dữ liệu là bao nhiêu bằng cách thay
TableList={1..12}
bằng
TableList = {1..List.Count(ColumnList)/3}
Trường hợp 2 loại số liệu như số lượng/ thành tiền, doanh thu/chi phí, mỗi nhóm là 2, 3 cột số liệu nối tiếp nhau thì việc unpivot trở nên khó khăn.
Với code sau đây dùng vòng lặp để Select Column khiến cho code nhẹ nhàng hơn rất nhiều. FIle đính kèm có dữ liệu tồn của 12 tháng, mỗi tháng 3 cột, tổng 36 cột.
Trường hợp 1: Tên cột có quy luật như Ma01, SL01, TT01, Ma02, SL02, TT02, ... Ma12, SL12, TT12:
Dùng cấu trúc tên cột để select đúng tên cột, ví dụ "Ma" & n với n từ 1 đến 12 và định dạng "00", tương tự là SL và TT
PHP:
let
MonthList={1..12},
Source = List.Transform(MonthList, (i) =>
let
Tablei=Table.SelectColumns(AllData,{"Ma" & Text.PadStart(Text.From(MonthList{i-1}),2,"0"),
"SL" & Text.PadStart(Text.From(MonthList{i-1}),2,"0"),
"TTien" & Text.PadStart(Text.From(MonthList{i-1}),2,"0")}),
Tablei1=Table.AddColumn(Table.RenameColumns(Tablei,{{"Ma" & Text.PadStart(Text.From(MonthList{i-1}),2,"0"),"Ma"},{ "SL" &
Text.PadStart(Text.From(MonthList{i-1}),2,"0"),"SL"},{"TTien" & Text.PadStart(Text.From(MonthList{i-1}),2,"0"),"TTien"}}),"Month",
each "Th" & Text.PadStart(Text.From(MonthList{i-1}),2,"0"))
in Tablei1),
Source1=Table.FromList(Source,Splitter.SplitByNothing()),
Source2 = Table.ExpandTableColumn(Source1,"Column1",Table.ColumnNames(Source{0})),
Result=Table.SelectRows(Source2, each [Ma] <> null)
in
ResultTrường hợp 2: Tên cột không có quy luật nhưng vẫn theo thứ tự mỗi tháng 3 cột, tuần tự đúng ý nghĩa dữ liệu các cột là Ma, SL và TT dù tên không quy luật (ví dụ Mã1, Ma02, Ma3, Mã 4)
Trường hợp này lấy 1 List tên cột (36 cột), dùng công thức biến đổi biến i (từ 1 đến 12) thành những thứ tự tương ứng từ 1 đến 36, dùng để select.
PHP:
let
ColumnList=Table.ColumnNames(AllData),
TableList={1..12},
Source = List.Transform(TableList, (i) =>
let
Col1 = ColumnList{(i-1) * 3 },
Col2 = ColumnList{(i-1) * 3 +1},
Col3 = ColumnList{(i-1) * 3 +2},
Source0 = Table.SelectColumns(AllData,{Col1,Col2,Col3}),
Source1 = Table.AddColumn(Source0, "Tháng", each "Th" & Text.PadStart(Text.From(TableList{i-1}),2, "0")),
Source2 = Table.RenameColumns(Source1,{{Col1,"Ma"},{Col2,"SL"},{Col3,"TTien"}})
in
Source2),
SourceTable=Table.FromList(Source, Splitter.SplitByNothing()),
ColList = Table.ColumnNames(SourceTable[Column1]{0}),
Result = Table.SelectRows(Table.ExpandTableColumn(SourceTable,"Column1",ColList),each [Ma] <> null)
in
ResultTableList={1..12}
bằng
TableList = {1..List.Count(ColumnList)/3}
Lần chỉnh sửa cuối:
