只是按照字面意思讲第一个期望实现
即除流水号外的其他列数据值均相同的行找了出来
1. 从当前工作簿建立查询
2. 将各列数据类型修改为text
3. 将各列中为null的值替换为-A-
4. 合并除流水号外的列
5. 利用Table.Group()实现相同数据的流水号数据分组
6. 保留流水号分组列 删除其他列
7. 关闭并上载以下为代码：
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"流水号", type text}, {"4.98707E+12", type text}, {"4.54985E+12", type text}, {"4.51506E+12", type text}, {"4.51506E+122", type text}, {"4515061186946", type text}, {"8801046286722", type text}, {"4.51506E+123", type text}, {"8801046286920", type text}, {"8.80105E+12", type text}, {"8.80105E+124", type text}, {"8.80105E+125", type text}, {"8.8095E+12", type text}, {"8801046286906", type text}, {"8.8095E+126", type text}, {"8.80105E+127", type text}, {"8.8095E+128", type text}, {"8.80105E+129", type text}, {"8.80105E+1210", type text}, {"8.8095E+1211", type text}, {"8.80105E+1212", type text}, {"8.8095E+1213", type text}, {"8.80105E+1214", type text}, {"8.8095E+1215", type text}, {"8.80105E+1216", type text}, {"8.8095E+1217", type text}, {"8.8095E+1218", type text}, {"8.8095E+1219", type text}, {"8.8095E+1220", type text}, {"8.8095E+1221", type text}, {"8.8095E+1222", type text}, {"8.80105E+1223", type text}, {"8.80105E+1224", type text}, {"8.8095E+1225", type text}, {"8.8095E+1226", type text}, {"种类", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,"-A-",Replacer.ReplaceValue,{"4.98707E+12", "4.54985E+12", "4.51506E+12", "4.51506E+122", "4515061186946", "8801046286722", "4.51506E+123", "8801046286920", "8.80105E+12", "8.80105E+124", "8.80105E+125", "8.8095E+12", "8801046286906", "8.8095E+126", "8.80105E+127", "8.8095E+128", "8.80105E+129", "8.80105E+1210", "8.8095E+1211", "8.80105E+1212", "8.8095E+1213", "8.80105E+1214", "8.8095E+1215", "8.80105E+1216", "8.8095E+1217", "8.8095E+1218", "8.8095E+1219", "8.8095E+1220", "8.8095E+1221", "8.8095E+1222", "8.80105E+1223", "8.80105E+1224", "8.8095E+1225", "8.8095E+1226"}),
#"Inserted Merged Column" = Table.AddColumn(#"Replaced Value", "Merged", each Text.Combine({[#"4.98707E+12"], [#"4.54985E+12"], [#"4.51506E+12"], [#"4.51506E+122"], [4515061186946], [8801046286722], [#"4.51506E+123"], [8801046286920], [#"8.80105E+12"], [#"8.80105E+124"], [#"8.80105E+125"], [#"8.8095E+12"], [8801046286906], [#"8.8095E+126"], [#"8.80105E+127"], [#"8.8095E+128"], [#"8.80105E+129"], [#"8.80105E+1210"], [#"8.8095E+1211"], [#"8.80105E+1212"], [#"8.8095E+1213"], [#"8.80105E+1214"], [#"8.8095E+1215"], [#"8.80105E+1216"], [#"8.8095E+1217"], [#"8.8095E+1218"], [#"8.8095E+1219"], [#"8.8095E+1220"], [#"8.8095E+1221"], [#"8.8095E+1222"], [#"8.80105E+1223"], [#"8.80105E+1224"], [#"8.8095E+1225"], [#"8.8095E+1226"], [种类]}, ""), type text),
Group = Table.Group(#"Inserted Merged Column","Merged",{"Duplicated Group",each Text.Combine([流水号],",")}),
#"Filtered Rows" = Table.SelectRows(Group, each ([Duplicated Group] = "0082487,0082502,0082507,0082508,0082511,0082513,0082639,0082645,0082646,0082650,0082653,0082656" or [Duplicated Group] = "0082494,0082495,0082498,0082503,0082505,0082506,0082510,0082512,0082516,0082520,0082521,0082522,0082525,0082526,0082528,0082534,0082537,0082538,0082540,0082660,0082673,0082742,0082803" or [Duplicated Group] = "0082497,0082501,0082504,0082515,0082517,0082518,0082523,0082524,0082527,0082529,0082530,0082531,0082532,0082659,0082667,0082674" or [Duplicated Group] = "0082643,0082649,0082671,0082672,0082678" or [Duplicated Group] = "0082863,0082868,0082874" or [Duplicated Group] = "0082870,0082871,0082872,0082873" or [Duplicated Group] = "0082878,0082879,0082881,0082882,0082884,0082885,0082887,0082891,0082894,0082898,0082916" or [Duplicated Group] = "0082897,0082905" or [Duplicated Group] = "0082924,0082925" or [Duplicated Group] = "0082981,0082983,0082985,0082988,0083007" or [Duplicated Group] = "0083063,0083068,0083070" or [Duplicated Group] = "0083090,0083091,0083100,0083103" or [Duplicated Group] = "0083094,0083097,0083107,0083136" or [Duplicated Group] = "0083169,0083171,0083175" or [Duplicated Group] = "0083170,0083173" or [Duplicated Group] = "0083172,0083179,0083188,0083189" or [Duplicated Group] = "0083176,0083180,0083187" or [Duplicated Group] = "0083177,0083178,0083181,0083182,0083186" or [Duplicated Group] = "0083183,0083184" or [Duplicated Group] = "0083666,0083669,0083673,0083674,0083679" or [Duplicated Group] = "0083683,0083699,0083719,0083730,0083978,0084135" or [Duplicated Group] = "0083694,0083695,0083698,0083701,0083718,0083740" or [Duplicated Group] = "0083919,0083931,0083959,0083968,0084017,0084018" or [Duplicated Group] = "0083925,0083928,0083934,0083947,0084064,0084176" or [Duplicated Group] = "0084005,0084129,0084177,0084181" or [Duplicated Group] = "0084011,0084123" or [Duplicated Group] = "0084035,0084051,0084100,0084156,0084173,0084178" or [Duplicated Group] = "0084043,0084048")),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Merged"})
in
#"Removed Columns"
11039111814362.zip
(32.32 KB, 下载次数: 0)
|
[复制链接]
