duplicates - 将列转换为多行数据到电源查询中具有多列的行-我在Power Query中需要这个
问题描述
Sub ConsolidateRows_SpreadAcross()
Dim lastRow As Long, i As Long, j As Long
Dim colMatch As Variant, colConcat As Variant
application.ScreenUpdating = False 'disable ScreenUpdating to avoid screen flashes
lastRow = range("A" & Rows.Count).End(xlUp).Row 'get last row
For i = lastRow To 2 Step -1
If Cells(i, 2) = Cells(i - 1, 2) Then
range(Cells(i, 3), Cells(i, Columns.Count).End(xlToLeft)).Copy Cells(i - 1, Columns.Count).End(xlToLeft).Offset(, 1)
Rows(i).Delete
Else
If Cells(i, 1) = Cells(i - 1, 1) Then
range(Cells(i, 2), Cells(i, Columns.Count).End(xlToLeft)).Copy _
Cells(i - 1, Columns.Count).End(xlToLeft).Offset(, 1)
Rows(i).Delete
End If
End If
Next
application.ScreenUpdating = True 'reenable ScreenUpdating
End Sub
解决方案
如果有机会,这可以满足您的需求,代码如下
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Column1"}, "Attribute", "Value"),
#"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Column1"}, {{"Count", each Table.AddIndexColumn(_, "Index", 1, 1), type table}}),
#"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Value", "Index"}, {"Value", "Index"}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded Count", {{"Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Expanded Count", {{"Index", type text}}, "en-US")[Index]), "Index", "Value")
in #"Pivoted Column"
推荐阅读
- typescript - 在不了解 TypeScript 的情况下使用 cordova-plugin-playlist
- javascript - Javascript getElementById 未定义
- python - 如何在 keras 中进行“树搜索”样式
- python - 如何估计高斯混合的一般协方差
- powerbi - PowerBI--自定义列--Multiple Condition IF statements
- bash - bash 逐行同时循环遍历 2 个文件
- python - 如何使用字符串作为输入从 Matlab 调用 Matlab 函数
- excel - 为什么 Excel 内存不足?
- angular - Angular 6 ChartJS 动态创建画布并在其上创建图形 - 一个例子?
- amazon-ec2 - boto3如何从实例中检索弹性IP