表结构转置

chenxuefu

各位大佬这个怎么转置

rarnma

仅供参考。

123-456789

友情参与！

luzwales

1. let
   
2.     Source = let m = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
   
3. n = Table.AddColumn(Table.AddIndexColumn(Table.AddColumn(m,"id",each if [a]="户主" then 1 else null),"Index",0),"Y",each if [id]=1 then [Index] else null),
   
4. o = Table.RowCount(m),
   
5. p = List.RemoveNulls(n[Y]),
   
6. q = List.Transform({0..List.Count(p)-1},each if _<List.Count(p)-1 then p{_+1}-p{_} else o-p{_}),
   
7. r = List.Zip({p,q})
   
8. in Table.Transpose(
   
9.     Table.FromColumns(
   
10.         List.Transform({0..List.Count(p)-1},each List.Combine(Table.ToRows(Table.Range(m,r{_}{0},r{_}{1}))))))
    
11. 
    
12. in
    
13.     Source

复制代码

gxb0717

工作簿11.zip (30.21 KB, 下载次数: 0)

2022-4-18 13:57 上传

点击文件名下载附件

vermouth7788

1. let
   
2.     Source = Excel.CurrentWorkbook(){[Name="表1"]}[Content],
   
3.     #"Changed Type" = Table.TransformColumnTypes(Source,{{"列1", type text}, {"列2", type text}, {"列3", type text}}),
   
4.     #"Grouped Rows" = Table.Combine(Table.Group(#"Changed Type", {"列1"}, {"a", each Table.FromRows({List.Combine(Table.ToRows(_))} )}, 0 , (x,y) => Number.From(y[列1]="户主"))[a])
   
5. in
   
6.     #"Grouped Rows"

复制代码