group-by - 在 Power Query 中“按所有行分组”操作后删除第一列

Question

尝试按第一列Podrocje_dela (PHASE 1) 分组时遇到问题。我使用 Group by All 行，其中第二列的名称为Aktivnosti。

阶段1

因为结果是两列格式（PHASE 2），但我只想要最后一列，所以应该删除用蓝色笔标记的那一列。

阶段2

为什么这是一个问题？我需要第一列项目作为列名称（红色），但这些名称也显示为从第一列和第二列（PHASE 3）组合的列值（蓝色），逗号分隔。我只需要第二列值，如最后一张图片（第 4 阶段）所示。

第三阶段

最终结果应如下图所示（第 4 阶段）。这个结果是通过 .AfterDelimiter 操作完成的，但是由于不同的第一列项目（PHASE 1）的数量可以改变它不够灵活。

第 4 阶段

最后，我需要 PHASE 1 的 PHASE 4 所示的结果。也许我的方法不是最好的，我很乐意提出任何建议。

我目前的代码是

let
Source = Access.Database(File.Contents("\\fs-srv01\PROJEKTI\S-DELOVNI-PROCES\DELOVNE URE\MS_Access\Podatkovne_baze\Organizacija.accdb"), [CreateNavigationProperties=true]),
_tblPodrocja_dela = Source{[Schema="",Item="tblPodrocja_dela"]}[Data],
#"Removed Columns" = Table.RemoveColumns(_tblPodrocja_dela,{"tblPodrocjaDela_Aktivnosti"}),
#"Merged Queries" = Table.NestedJoin(#"Removed Columns",{"ID_Podrocje_dela"},tblPodrocjaDela_Aktivnosti_M2M,{"ID_Podrocje_dela"},"tblPodrocjaDela_Aktivnosti_M2M",JoinKind.FullOuter),
#"Expanded tblPodrocjaDela_Aktivnosti_M2M" = Table.ExpandTableColumn(#"Merged Queries", "tblPodrocjaDela_Aktivnosti_M2M", {"ID_Aktivnost"}, {"tblPodrocjaDela_Aktivnosti_M2M.ID_Aktivnost"}),
#"Merged Queries1" = Table.NestedJoin(#"Expanded tblPodrocjaDela_Aktivnosti_M2M",{"tblPodrocjaDela_Aktivnosti_M2M.ID_Aktivnost"},tblAktivnostiVsakodnevne,{"ID_aktivnost"},"tblAktivnosti",JoinKind.LeftOuter),
#"Expanded tblAktivnosti" = Table.ExpandTableColumn(#"Merged Queries1", "tblAktivnosti", {"Aktivnost"}, {"tblAktivnosti.Aktivnost"}),
#"Removed Columns1" = Table.RemoveColumns(#"Expanded tblAktivnosti",{"ID_Podrocje_dela", "tblPodrocjaDela_Aktivnosti_M2M.ID_Aktivnost"}),
#"Grouped Rows" = Table.Group(#"Removed Columns1", {"Podrocje_dela"}, {{"Aktivnosti", each _, type table}}),
toList = Table.TransformColumns(#"Grouped Rows", {"Aktivnosti", Table.ToList}),
output = #table(toList[Podrocje_dela],List.Zip(toList[Aktivnosti])),
#"Extracted Text After Delimiter" = Table.TransformColumns(output, {{"Finance", each Text.AfterDelimiter(_, ","), type text}, {"IT", each Text.AfterDelimiter(_, ","), type text}, {"Kadrovska", each Text.AfterDelimiter(_, ","), type text}, {"Komerciala", each Text.AfterDelimiter(_, ","), type text}, {"Podporne službe", each Text.AfterDelimiter(_, ","), type text}, {"Proizvodnja", each Text.AfterDelimiter(_, ","), type text}, {"Raziskave in razvoj", each Text.AfterDelimiter(_, ","), type text}, {"Vodstvo", each Text.AfterDelimiter(_, ","), type text}})

谢谢你们！

标签： group-bypowerquery

Answer 1

如果有人有同样的问题。我找到了解决我的问题的方法。要仅对单个列进行分组，我指定了要指定的列：

从

#"Grouped Rows" = Table.Group(#"Removed Columns1", {"Podrocje_dela"}, {{"Aktivnosti", each _, type table}}),

至

#"Grouped Rows" = Table.Group(#"Removed Columns1", {"Podrocje_dela"}, {{"Aktivnosti", each _[Aktivnosti], type table}}),

具有升序排序的 Power Query 代码，其中 null 位于末尾：

let
Source = Access.Database(File.Contents("\\fs-srv01\PROJEKTI\S-DELOVNI-PROCES\DELOVNE URE\MS_Access\Podatkovne_baze\Organizacija.accdb"), [CreateNavigationProperties=true]),
_tblPodrocjaDela_Aktivnosti = Source{[Schema="",Item="tblPodrocjaDela_Aktivnosti"]}[Data],
#"Removed Columns" = Table.RemoveColumns(_tblPodrocjaDela_Aktivnosti,{"ID_Podrocje_dela", "ID_Aktivnost"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"tblPodrocja_dela", "tblAktivnosti"}),
#"Expanded tblPodrocja_dela" = Table.ExpandRecordColumn(#"Reordered Columns", "tblPodrocja_dela", {"Podrocje_dela"}, {"Podrocje_dela"}),
#"Expanded tblAktivnosti" = Table.ExpandRecordColumn(#"Expanded tblPodrocja_dela", "tblAktivnosti", {"Aktivnost", "Vsakodnevna"}, {"Aktivnosti", "tblAktivnosti.Vsakodnevna"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded tblAktivnosti", each true),
#"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Aktivnosti", Order.Ascending}}),
#"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows",{"tblAktivnosti.Vsakodnevna"}),
#"Grouped Rows" = Table.Group(#"Removed Columns1", {"Podrocje_dela"}, {{"Aktivnosti", each _[Aktivnosti], type table}}),
toList  = Table.Sort(#"Grouped Rows",{{"Podrocje_dela", Order.Ascending}}),
MyColumnNames = toList[Podrocje_dela],
arrangedDataFromAccess = #table(MyColumnNames ,List.Zip(toList[Aktivnosti])),

SortAllColumns = Table.FromColumns(List.Generate(()=>
                [x=0,y=Table.Column(arrangedDataFromAccess  ,MyColumnNames{x})],
                each [x] < List.Count(MyColumnNames),
                each [x=[x]+1,y=Table.Column(arrangedDataFromAccess  ,MyColumnNames{x})],
                each List.Sort([y] ,(xx,yy)=>if xx&yy<> null then Value.Compare(xx ,yy) else Value.Compare(yy ,xx))),MyColumnNames),
    


CountItemsInColumns = List.Generate(()=>
                [x=0,y=Table.Column(arrangedDataFromAccess  ,MyColumnNames{x})],
                each [x] < List.Count(MyColumnNames),
                each [x=[x]+1,y=Table.Column(arrangedDataFromAccess  ,MyColumnNames{x})],
                each List.NonNullCount([y])), 
CountItemsInColumnsTransposed  = Table.Transpose(Table.FromList(CountItemsInColumns,Splitter.SplitByNothing()), MyColumnNames),

finalDataFromAccess = Table.Combine({CountItemsInColumnsTransposed, SortAllColumns}) in finalDataFromAccess